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.

5 Answers 5

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

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

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
---use sp_hlepdb, schedule it and dump it in a table.

--here you go

---step1
USE [msdb]
GO

/****** Object:  Table [dbo].[database_growth]    Script Date: 2/4/2015 11:47:17 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[database_growth](
    [dbname] [sysname] NOT NULL,
    [owner] [sysname] NULL,
    [created] [nvarchar](11) NULL,
    [dbid] [smallint] NULL,
    [dbdesc] [nvarchar](600) NULL,
    [dbsize] [nvarchar](13) NULL,
    [cmptlevel] [tinyint] NULL,
    [run_date] [datetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]

GO

---setp2
USE [msdb]
GO

/****** Object:  Job [Database Growth Tracking]    Script Date: 2/4/2015 11:51:21 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2/4/2015 11:51:21 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Database Growth Tracking', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'Track Database Growth daily', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [FATCH DB SIZE INFO PUT INTO DATABASE GROWTH TABLE IN MSDB]    Script Date: 2/4/2015 11:51:21 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'FATCH DB SIZE INFO PUT INTO DATABASE GROWTH TABLE IN MSDB', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'USE
MSDB
INSERT INTO  database_growth
EXEC sp_helpdb2
--- EXEC [dbo].[SP_HELPDB2]', 
        @database_name=N'msdb', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'fatch all db size info', 
        @enabled=1, 
        @freq_type=16, 
        @freq_interval=1, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20140731, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959, 
        @schedule_uid=N'49a0ae34-40c7-4d9b-912a-8ea5df6d6e62'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

NADIR

share|improve this answer

Your Answer

 
discard

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