How To Check The Growth Of SQL Database

A simple way is to have a log table, updated nightly. Just create a table and a stored proc as below and have a job that runs it every night.

You can set a database auto-growth setting by using SQL Server Management Studio, scripted SMO, or by using T-SQL when you create your database. You can also use these methods to change the auto-growth settings of existing databases. Let me show you how to check the auto-grow database size using SQL Server Management Studio.

SELECT name AS FileName, size*1.0/128 AS FileSizeinMB,
'MaximumSizeinMB' = CASE max_size
       WHEN 0 THEN 'No growth is allowed.'
       WHEN -1 THEN 'Autogrowth is on.'
       WHEN 268435456
          THEN 'Log file will grow to a maximum size of 2 TB.''
       ELSE CAST (max_size*1.0/128 AS nvarchar(30))
    END,
growth AS 'GrowthValue',
'GrowthIncrement' =
    CASE
       WHEN growth = 0 THEN 'File size is fixed and will not grow.'
       WHEN growth > 0 AND is_percent_growth = 0
          THEN 'Growth value is in units of 8-KB pages.'
       ELSE 'Growth value is a percentage.'
    END
FROM sys.database_files

The example here runs the size query twice for two different databases on the same server. You can then have a simple report off the back of this showing the growth trends over time and on a weekly basis for the largest and the fastest-growing tables.

Table:

CREATE TABLE [dbo].[tb_TableSize](
    [Id] [int] IDENTITY(1000,1) NOT NULL,
    [DB] [varchar](2) NOT NULL,
    [table_id] [int] NOT NULL,
    [table_name] [sysname] NOT NULL,
    [rows] [int] NULL,
    [total_space_MB] [int] NULL,
    [data_space_MB] [int] NULL,
    [index_space_MB] [int] NULL,
    [unused_space_MB] [int] NULL,
    [query_date] [smalldatetime] NULL,
 CONSTRAINT [PK_tb_TableSize] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Procedure:

CREATE PROCEDURE [Job].[proc_TableSizeINSERT]

AS
BEGIN

set nocount on

declare @dt smalldatetime
set @dt = getutcdate()

    INSERT INTO [CommunicatorV4DataWarehouse].[dbo].[tb_TableSize]
               ([DB]
               ,[table_id]
               ,[table_name]
               ,[rows]
               ,[total_space_MB]
               ,[data_space_MB]
               ,[index_space_MB]
               ,[unused_space_MB]
               ,[query_date])

    SELECT
            'V4' as DB,
            table_id = [object_id],
            table_name = [name],
            rows = [rowCount],
            total_space_MB = reservedpages * 8/1000,
            data_space_MB = pages * 8/1000,
            index_space_MB = (CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8/1000,
            unused_space_MB = (CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8/1000,
            query_date = @dt
    from (
        SELECT  o.[Name], [object_id],
            reservedpages = SUM (reserved_page_count),
            usedpages = SUM (used_page_count),
            pages = SUM (
                CASE
                    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                    ELSE lob_used_page_count + row_overflow_used_page_count
                END
                ),
            [rowCount] = SUM (
                CASE
                    WHEN (index_id < 2) THEN row_count
                    ELSE 0
                END
                )
        FROM CommunicatorV4.sys.dm_db_partition_stats s inner join CommunicatorV4..sysobjects o on s.[object_id] = o.id
        where type = 'U'
        group by [object_id], o.[name]
    ) DBData


    INSERT INTO [CommunicatorV4DataWarehouse].[dbo].[tb_TableSize]
               ([DB]
               ,[table_id]
               ,[table_name]
               ,[rows]
               ,[total_space_MB]
               ,[data_space_MB]
               ,[index_space_MB]
               ,[unused_space_MB]
               ,[query_date])

    SELECT
            'DW' as DB,
            table_id = [object_id],
            table_name = [name],
            rows = [rowCount],
            total_space_MB = reservedpages * 8/1000,
            data_space_MB = pages * 8/1000,
            index_space_MB = (CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8/1000,
            unused_space_MB = (CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8/1000,
            query_date = @dt
    from (
        SELECT  o.[Name], [object_id],
            reservedpages = SUM (reserved_page_count),
            usedpages = SUM (used_page_count),
            pages = SUM (
                CASE
                    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                    ELSE lob_used_page_count + row_overflow_used_page_count
                END
                ),
            [rowCount] = SUM (
                CASE
                    WHEN (index_id < 2) THEN row_count
                    ELSE 0
                END
                )
        FROM CommunicatorV4DataWarehouse.sys.dm_db_partition_stats s inner join CommunicatorV4DataWarehouse..sysobjects o on s.[object_id] = o.id
        where type = 'U'
        group by [object_id], o.[name]
    ) DBData

--truncate table CommunicatorV4DataWarehouse.dbo.tb_TableSize

END