SQL Server – Space – Data And Log File Growth Auto-Grow History TSQL

I apologize for the lull in my blogging. After most of my blog posts were completely ripped off by a Chinese website (full copy/paste or web-crawl and replace my name type plagiarism) a switch just flipped in my brain. I am trying again after submitting complaints with Google (Thanks to Brent Ozar for the guidance) and removing them from the top of the search results for now although they continue to operate fine.

With is really simple post today, I wanted to show you how to see the space growth history in SQL Server. Please check out my related post on how to do space analysis with PowerBI and PowerShell.

How to get auto-grow events history

There is the easy UI method and the custom query method. Basically, we want something like this produced by the “Method 1” report below.

Method 1 – Disk Usage Report:

The easiest way is to use the built-in SSMS report.

• Right click on the database
• Report
• Disk usage
• Expand “Data/Log Files Autogrow Shrink Events”

Method 2 – TSQL Query:

This is a method that you can use if you want programmatic data capture. I just did a trace to get the SQL from what SSMS issued under the covers (ripped off from MS! ;-)).

Query 1- Overall space summary (shown at the top of the report)

BEGIN try
    DECLARE @dbsize BIGINT
    DECLARE @logsize BIGINT
    DECLARE @database_size_mb FLOAT
    DECLARE @unallocated_space_mb FLOAT
    DECLARE @reserved_mb FLOAT
    DECLARE @data_mb FLOAT
    DECLARE @log_size_mb FLOAT
    DECLARE @index_mb FLOAT
    DECLARE @unused_mb FLOAT
    DECLARE @reservedpages BIGINT
    DECLARE @pages BIGINT
    DECLARE @usedpages BIGINT 

    SELECT @dbsize = Sum(CONVERT(BIGINT, CASE
                                           WHEN status & 64 = 0 THEN size
                                           ELSE 0
                                         END)),
           @logsize = Sum(CONVERT(BIGINT, CASE
                                            WHEN status & 64 != 0 THEN size
                                            ELSE 0
                                          END))
    FROM   dbo.sysfiles 

    SELECT @reservedpages = Sum(a.total_pages),
           @usedpages = Sum(a.used_pages),
           @pages = Sum(CASE
                          WHEN it.internal_type IN ( 202, 204 ) THEN 0
                          WHEN a.type != 1 THEN a.used_pages
                          WHEN p.index_id < 2 THEN a.data_pages                            ELSE 0                          END)
    FROM sys.partitions p
           JOIN sys.allocation_units a
             ON p.partition_id = a.container_id
           LEFT JOIN sys.internal_tables it
                  ON p.object_id = it.object_id

    SELECT @reserved_mb = @reservedpages * 8192 / 1048576.0 

    SELECT @data_mb = @pages * 8192 / 1048576.0 

    SELECT @log_size_mb = CONVERT(DEC(19, 2), @logsize) * 8192 / 1048576.0 

    SELECT @index_mb = ( @usedpages - @pages ) * 8192 / 1048576.0 

    SELECT @unused_mb = ( @reservedpages - @usedpages ) * 8192 / 1048576.0 

    SELECT @database_size_mb                        AS 'database_size_mb',
           @reserved_mb                             AS 'reserved_mb',
           @unallocated_space_mb                    AS 'unallocated_space_mb',
           ( @reserved_mb + @unallocated_space_mb ) AS 'data_size',
           @log_size_mb                             AS 'transaction_log_size',
           Cast(@unallocated_space_mb * 100.0 / (
                @reserved_mb + @unallocated_space_mb ) AS
                DECIMAL(10, 2))                     AS 'unallocated',
           Cast(@reserved_mb * 100 / ( @reserved_mb + @unallocated_space_mb ) AS
                DECIMAL(10, 2))                     AS 'reserved',
           Cast(@data_mb * 100 / ( @reserved_mb + @unallocated_space_mb ) AS
                DECIMAL
                (10, 2)
           )                                        AS 'data',
           Cast(@index_mb * 100 / ( @reserved_mb + @unallocated_space_mb ) AS
                DECIMAL(10, 2))                     AS 'index_1',
           Cast(@unused_mb * 100 / ( @reserved_mb + @unallocated_space_mb ) AS
                DECIMAL(10, 2))                     AS 'unused';
END try 

BEGIN catch
    SELECT 1                AS database_size_mb,
           Error_number()   AS reserved_mb,
           Error_severity() AS unallocated_space_mb,
           Error_state()    AS data_size,
           1                AS transaction_log_size,
           Error_message()  AS unallocated,
           -100             AS reserved,
           1                AS data,
           1                AS index_1,
           1                AS unused
END catch

Query 2 – Auto-growth events shown at the bottom of the image

As you can see the data comes from reading the default trace file using the function fn_trace_gettable and filtering it for EventClasses between 92 and 95 that correspond to auto-growth!

begin try
	if (select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1
	begin
		declare @curr_tracefilename varchar(500) ;
		declare @base_tracefilename varchar(500) ;
		declare @indx int ;

		select @curr_tracefilename = path from sys.traces where is_default = 1 ;
		set @curr_tracefilename = reverse(@curr_tracefilename);
		select @indx  = patindex('%\%', @curr_tracefilename) ;
		set @curr_tracefilename = reverse(@curr_tracefilename) ;
		set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;

		select  (dense_rank() over (order by StartTime desc))%2 as l1
		,       convert(int, EventClass) as EventClass
		,       DatabaseName
		,       Filename
		,       (Duration/1000) as Duration
		,       StartTime
		,       EndTime
		,       (IntegerData*8.0/1024) as ChangeInSize
		from ::fn_trace_gettable( @base_tracefilename, default )
		left outer join sys.databases as d on (d.name = DB_NAME())
		where EventClass >=  92      and EventClass <=  95        and ServerName = @@servername   and DatabaseName = db_name()  and (d.create_date < EndTime)
		order by StartTime desc ;
	end
	else
		select -1 as l1, 0 as EventClass, 0 DatabaseName, 0 as Filename, 0 as Duration, 0 as StartTime, 0 as EndTime,0 as ChangeInSize
end try
begin catch
	select -100 as l1
	,       ERROR_NUMBER() as EventClass
	,       ERROR_SEVERITY() DatabaseName
	,       ERROR_STATE() as Filename
	,       ERROR_MESSAGE() as Duration
	,       1 as StartTime, 1 as EndTime,1 as ChangeInSize
end catch

Below is a screenshot of the TSQL query results. Event class 93 corresponds to Log file auto-growth and 92 for Data file.

I know that was a really quick post but I hope that you find it useful.

Advertisement