Query to Display Recent Data and TLog File Autogrowth and Autoshrink Events   2 comments

Here is a query to read the default trace on your SQL instance and produce a report of recent Data and TLog file autogrowth and autoshrink events that have occurred for that SQL instance. The query works for SQL 2005 and above, and there are no edits required.

The query is a modified version of the one used by the SQL 2008 R2 Management Studio’s “Standard Report” named “Disk Usage,” which has a section that displays this data (Object Explorer –> right click on database –> Reports –> Standard Reports –> “Disk Usage”).

Just open a query window connected to the SQL instance you wish to report on and run the query. This version displays data for all databases, including the database name, the date of the autogrowth event, the number of milliseconds it took, and the size of the growth in MB.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

--NE - 6/17/2011

--Query to display recent Data and TLog file autogrowth and autoshrink events
--for all databases on the instance. Based on query used by the SSMS Standard
--Report named "Disk Usage" which has a section that displays this data. Data
--is retrieved from the default trace.

--Works for SQL 2005 and above.

USE [master]
GO

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,
			ServerName AS [SQL_Instance],
			--CONVERT(INT, EventClass) AS EventClass,
			DatabaseName AS [Database_Name],
			Filename AS [Logical_File_Name],
			(Duration/1000) AS [Duration_MS],
			CONVERT(VARCHAR(50),StartTime, 100) AS [Start_Time],
			--EndTime,
			CAST((IntegerData*8.0/1024) AS DECIMAL(19,2)) AS [Change_In_Size_MB]
		FROM ::fn_trace_gettable(@base_tracefilename, default)
		WHERE 
			EventClass >=  92
			AND EventClass <=  95
			--AND ServerName = @@SERVERNAME
			--AND DatabaseName = 'myDBName'  
		ORDER BY DatabaseName, 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

Posted June 21, 2011 by Norm Enger in Microsoft SQL Server

Advertisements

Posted June 21, 2011 by Norm Enger in Microsoft SQL Server

2 responses to “Query to Display Recent Data and TLog File Autogrowth and Autoshrink Events

Subscribe to comments with RSS.

  1. Man, Norm. This is one NICE script. Thanks so much. I am going to mention it on Google+. I did not see you on there, but I will credit you for sure.

  2. This is just what I needed. Thanks!

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: