Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Nov 12 '11 at 0:14

This question came from our site for professional and enthusiast programmers.

4 Answers 4

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.

share|improve this answer

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]
share|improve this answer

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
share|improve this answer
    
I mean as a DBA how to check the growth of database size? –  user1032394 Nov 11 '11 at 12:05
1  
If you have SQL Server Management Studio, right-click the database ->Properties->Files tab –  misha Nov 11 '11 at 12:15

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.