Database Space Usage

Database Space Usage

                                                    Today I have a request for T-SQL script needed for Database Space Usage and Estimated Space Need.I have decided to publish my database space usage script(this is only modified script not full script, full script contains table and index wise space usage in later article I have plan to publish it). following script,

• Calculated three decimal places (current situation we no need to but script will supports :) )
• You can create SQL Agent Job using following script and schedule it.
•  All data contains "tblDatabaseSpaceUsed" table and you can reuse it.

USE [master]
GO
DECLARE @ssql                nvarchar(MAX);
DECLARE @DatabaseName        sysname;
  
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDatabaseSpaceUsed]') AND [type] in (N'U'))
BEGIN
    CREATE TABLE [dbo].[tblDatabaseSpaceUsed](
        ROW_ID                [bigint] IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
        CUR_DATE            [date] NOT NULL,
        DATABASE_NAME        [sysname] NOT NULL,
        LOGICAL_NAME        [sysname] NOT NULL,
        FILE_SIZE_MB        [decimal](18, 3) NULL,
        SPACE_USED_MB        [decimal](18, 3) NULL,
        FREE_SPACE_MB        [decimal](18, 3) NULL,
        DB_FILE_NAME        [sysname] NOT NULL
    );

    CREATE CLUSTERED INDEX [CIX_tblDatabaseSpaceUsed_DB_NAME] ON [dbo].[tblDatabaseSpaceUsed]([DATABASE_NAME] ASC);

END


DECLARE CurDBSpace CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT name FROM sys.databases WHERE [state]=0 ORDER BY database_id;
  
OPEN CurDBSpace;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM CurDBSpace INTO @DatabaseName;
    IF @@FETCH_STATUS = -1 BREAK;
    SET @ssql = N'USE '
        + QUOTENAME(@DatabaseName)
        + CHAR(13)+ CHAR(10)
        + N'INSERT INTO [master].[dbo].[tblDatabaseSpaceUsed](CUR_DATE,DATABASE_NAME,LOGICAL_NAME,FILE_SIZE_MB,SPACE_USED_MB,FREE_SPACE_MB,DB_FILE_NAME)'+ CHAR(13)+ CHAR(10)+
    'SELECT
         GETDATE()
        ,DB_NAME()
        ,f.name
        ,CONVERT(decimal(12,2),round(f.size/128.000,3))
        ,CONVERT(decimal(12,2),round(fileproperty(f.name,''SpaceUsed'')/128.000,3))
        ,CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,''SpaceUsed''))/128.000,3))
        ,f.name
    FROM sys.database_files f;';

    EXEC sp_executesql @ssql;
  
END
CLOSE CurDBSpace;
DEALLOCATE CurDBSpace;

with using above script data. I have written for another small T-SQL script for get  daily Space usage and "Estimated Space Need". you can modify this for your own requirement with using "tblDatabaseSpaceUsed" this table, 

DECLARE @NextNoOfDays    smallint=30;

SELECT Prv.Prv_RowID RowID,Prv.CUR_DATE,ISNULL(Crnt.CUR_DATE,Prv.CUR_DATE) CUR_DATE,Prv.Prv_SPACE_USED_MB,ISNULL(Crnt.Crnt_SPACE_USED_MB,0) Crnt_SPACE_USED_MB,ISNULL(Crnt.Crnt_SPACE_USED_MB,Prv.Prv_SPACE_USED_MB)-Prv.Prv_SPACE_USED_MB DIFF_SPACE_USED_MB,
ISNULL(SUM(ISNULL(Crnt.Crnt_SPACE_USED_MB,Prv.Prv_SPACE_USED_MB)-Prv.Prv_SPACE_USED_MB) OVER(PARTITION BY NULL),1) /ISNULL(COUNT(*) OVER(PARTITION BY NULL),1)*@NextNoOfDays NextNoOfDaysEstimatedSpaceNeedMB
FROM
    (
        SELECT ROW_NUMBER() OVER(ORDER BY CUR_DATE) AS Prv_RowID,CUR_DATE,SUM(FILE_SIZE_MB) Prv_FILE_SIZE_MB,SUM(SPACE_USED_MB) Prv_SPACE_USED_MB,SUM(FREE_SPACE_MB) Prv_FREE_SPACE_MB
        FROM tblDatabaseSpaceUsed WHERE DATABASE_NAME='AdventureWorks'
        GROUP BY CUR_DATE
    ) Prv LEFT JOIN
    (
        SELECT (ROW_NUMBER() OVER(ORDER BY CUR_DATE)-1) AS Crnt_RowID,CUR_DATE,SUM(FILE_SIZE_MB) Crnt_FILE_SIZE_MB,SUM(SPACE_USED_MB) Crnt_SPACE_USED_MB,SUM(FREE_SPACE_MB) Crnt_FREE_SPACE_MB
        FROM tblDatabaseSpaceUsed WHERE DATABASE_NAME='AdventureWorks'
        GROUP BY CUR_DATE
    ) Crnt ON Prv.Prv_RowID=Crnt.Crnt_RowID

If you have many databases in your SQL Server instance then you have to done small amendments for above given T-SQL script as follows, 

DECLARE @NextNoOfDays    smallint=30;

SELECT Prv.Prv_DATABASE_NAME,Prv.CUR_DATE,ISNULL(Crnt.CUR_DATE,Prv.CUR_DATE) CUR_DATE,Prv.Prv_SPACE_USED_MB,ISNULL(Crnt.Crnt_SPACE_USED_MB,0) Crnt_SPACE_USED_MB,ISNULL(Crnt.Crnt_SPACE_USED_MB,Prv.Prv_SPACE_USED_MB)-Prv.Prv_SPACE_USED_MB DIFF_SPACE_USED_MB,
ISNULL(SUM(ISNULL(Crnt.Crnt_SPACE_USED_MB,Prv.Prv_SPACE_USED_MB)-Prv.Prv_SPACE_USED_MB) OVER(PARTITION BY Prv.Prv_DATABASE_NAME),1) /ISNULL(COUNT(*) OVER(PARTITION BY Prv.Prv_DATABASE_NAME),1)*@NextNoOfDays NextNoOfDaysEstimatedSpaceNeedMB
FROM
    (
        SELECT ROW_NUMBER() OVER(PARTITION BY DATABASE_NAME ORDER BY CUR_DATE) AS Prv_RowID,DATABASE_NAME Prv_DATABASE_NAME,CUR_DATE,SUM(FILE_SIZE_MB) Prv_FILE_SIZE_MB,SUM(SPACE_USED_MB) Prv_SPACE_USED_MB,SUM(FREE_SPACE_MB) Prv_FREE_SPACE_MB
        FROM tblDatabaseSpaceUsed WHERE DATABASE_NAME NOT IN('master','model','msdb')
        GROUP BY DATABASE_NAME,CUR_DATE
    ) Prv LEFT JOIN
    (
        SELECT (ROW_NUMBER() OVER(PARTITION BY DATABASE_NAME ORDER BY CUR_DATE)-1) AS Crnt_RowID,DATABASE_NAME Crnt_DATABASE_NAME,CUR_DATE,SUM(FILE_SIZE_MB) Crnt_FILE_SIZE_MB,SUM(SPACE_USED_MB) Crnt_SPACE_USED_MB,SUM(FREE_SPACE_MB) Crnt_FREE_SPACE_MB
        FROM tblDatabaseSpaceUsed WHERE DATABASE_NAME NOT IN('master','model','msdb')
        GROUP BY DATABASE_NAME,CUR_DATE
    ) Crnt ON Prv.Prv_RowID=Crnt.Crnt_RowID AND Prv.Prv_DATABASE_NAME=Crnt.Crnt_DATABASE_NAME
ORDER BY Prv.Prv_DATABASE_NAME

Cheers...
and Most welcome your comments and ideas...