SQL Server - Monitoring Table Size

There are many situations when we want to know the size of our database tables or just need a list of largest tables. The simplest way to get these details is use SQL Server Standard Report. Please refer the below screen shots to generate Standard report.This Report provides the fair amount of information and most of them are self explanatory.
SSMS>SelectDataBase>Reports>StandardReports>Disk Usage By Tables/Disk Usage By Top Tables

We are using Azure in our project and our database size is increasing rapidly. Our leadership team want a mechanism to predict the size of database in future, so then can estimate the cost of hosting our application in Azure. Database size is just one of the factor of the cost of cloud hosting.

As a solution we have decided to capture the size of tables every week and later we will be using that information for seeing the pattern of table growth in size. We have created a web job which is running a query and storing the result in a table. Please find the query below (I just found it on internet) and modified it according to our need (I cannot share final query due to corporate policy)-

Begin

 SET NOCOUNT ON
    IF OBJECT_ID('tempdb..#TableSizeMonitoring') IS NOT NULL DROP TABLE #TableSizeMonitoring

Begin

 SET NOCOUNT ON
    IF OBJECT_ID('tempdb..#TableSizeMonitoring') IS NOT NULL DROP TABLE #TableSizeMonitoring

    CREATE TABLE #TableSizeMonitoring
    (
    TableName sysname ,
    [Rows] int ,
    [Reserved] varchar(20),
    [Data] varchar(20),
    [Index_Size] varchar(20),
    [Unused] varchar(20),
    [Reserved_KB] bigint,
    [Data_KB] bigint,
    [Index_Size_KB] bigint,
    [Unused_KB] bigint
    )

    DECLARE @CMD nVarchar(MAX) =''
    SELECT @CMD +='EXEC sp_spaceused ' +  ''''+QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME(TABLE_NAME)+''''+';'+CHAR(10)
    FROM INFORMATION_SCHEMA.TABLES

     INSERT INTO #TableSizeMonitoring (TableName ,[Rows] , [Reserved], [Data] , [Index_Size] , [Unused] )
     EXEC sp_executesql @CMD


     UPDATE #TableSizeMonitoring
     SET [Reserved_KB] = CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Reserved] , ' KB', '')))),
         [Data_KB] = CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Data] , ' KB', '')))),
         [Index_Size_KB]= CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Index_Size] , ' KB', '')))),
         [Unused_KB]= CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Unused] , ' KB', ''))))


     SELECT TableName, [Rows], Reserved_KB , Data_KB , Index_Size_KB , Unused_KB ,  Data_KB / 1024.0 Data_MB , Data_KB / 1024.0 / 1024.0 Data_GB
     FROM #TableSizeMonitoring
     ORDER BY Data_KB DESC


     DROP TABLE #TableSizeMonitoring
End
--==========================================================
SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN    
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name

I know this is not a complete solution but I feel it can be used as framework for the purpose of monitoring the size of tables over time.

Thanks for reading the article !!!

Have A Great Day Ahead !!!