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.

DiskUsageAndAutoGrowEvents.jpg

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 = @reservedpages then (convert (dec (19,2),@dbsize) - convert (dec (19,2),@reservedpages)) * 8192 / 1048576.0
	else 0
	end)

	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.AutoGrowEventsTSQL.jpg

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s