How to check the growth of database

Question

I would like to know how to check the growth of entire database and individual data files.

Also, what to suggest to the client if the database is growing rapidly?

Answer 1

Is this what you are looking for? (more information here: http://msdn.microsoft.com/en-us/library/ms188233.aspx):

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

Answer 2

You can query the default trace to get information about recent growth events.

DECLARE @path nvarchar(260) = (
    SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 260)) +'log.trc'
    FROM    sys.traces
    WHERE   is_default = 1)

SELECT gt.DatabaseID,
       gt.FileName,
       COUNT(*) AS NumberOfEvents,
       CASE WHEN te.name LIKE'%Grow' THEN 1 ELSE 0 END AS is_growth_event
FROM  sys.fn_trace_gettable(@path, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE   te.name in ('Data File Auto Grow','Log File Auto Grow','Data File Auto Shrink','Log File Auto Shrink')
GROUP BY gt.DatabaseID,
       gt.FileName,
       te.name

You can also use Event Notifications to be notified on DATA_FILE_AUTO_GROW,LOG_FILE_AUTO_GROW without having to poll the trace.

Answer 3

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

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.

Stored Proc:

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


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]

Answer 4

If you're looking for the historic size of you database and you do full backups regularly you can find it by querying sysbackupset in msdb.

An example of how to do that is found here: http://theadeptdba.blogspot.com/2013/01/how-fast-is-my-sql-server-database.html