THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a Product Evangelist for SentryOne, makers of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide.

Reviewing AutoGrow events from the default trace

Since I don't have the luxury of setting up event notifications on all my servers, in SQL Server 2005 I can use the default trace to monitor autogrow events... this helps to prepare for increased disk space usage, and also lets me know if my log backups are happening frequently enough.

This is probably covered in a ton of other places, but the question comes up enough that I thought I would add my quick & dirty methodology. Here is a quick method that grabs the folder for the default trace from sys.traces, then passes that folder into sys.fn_trace_gettable.

DECLARE @path NVARCHAR(260);

SELECT
  
@path = REVERSE(SUBSTRING(REVERSE([path]),
  
CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
  
DatabaseName,
  
[FileName],
  
SPID,
  
Duration,
  
StartTime,
  
EndTime,
  
FileType = CASE EventClass
      
WHEN 92 THEN 'Data'
      
WHEN 93 THEN 'Log'
  
END
FROM
sys.fn_trace_gettable(@path, DEFAULT)
WHERE
  
EventClass IN (92,93)
ORDER BY
  
StartTime DESC;

 

Published Thursday, January 11, 2007 8:37 AM by AaronBertrand

Comments

 

oracledba said:

Good stuff.

June 2, 2008 11:53 AM
 

Ray said:

Great script! Thank you.

I did need to make one minor change. Had to enclose the path with double-quotes.

August 5, 2008 5:48 PM
 

Eoin said:

Excellent stuff

October 9, 2009 8:38 AM
 

SQLRocker said:

Right Click Database-Reports-Data Usage-Data/Log Files Autogrow/Autoshrink Events. Script is good for verification.

August 5, 2010 3:28 PM
 

Martin said:

You don't need to use xp_cmdshell for this. You can use

DECLARE @path NVARCHAR(260)

SELECT @path = REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 260)) + N'log.trc'

FROM    sys.traces

WHERE   is_default = 1

SELECT COUNT(*)

FROM sys.fn_trace_gettable(@path, DEFAULT)

July 21, 2011 10:06 AM
 

AaronBertrand said:

Thanks Martin. In fact the code sample I gave uses DEFAULT as the second parameter and this leads to counting events up to n times (where n is the number of trace files) depending on which file the event occurs in. I've corrected that.

July 21, 2011 10:21 AM
 

opc.three said:

Agreed. No need to use xp_CmdShell. Thanks Aaron for the initial solution and idea. Thanks Martin, I have removed the use of xp_CmdShell from my monitor process, always a welcome chore.

October 24, 2011 3:15 PM
 

Garry Bargsley said:

Is there a way to see what size the TempDB grew by?  The settings in the SSMS GUI do not match the setting I get returned from sp_helpfile.  I am trying to figure out which one is being used when autogrow kicks in.

March 30, 2012 4:06 PM
 

Massimo said:

You can add the column IntegerData/128

It says the size (in MB) the TempDB grew by

December 5, 2012 6:10 AM
 

Ganesh said:

Great script. Thanks Aaron and Martin.

December 14, 2012 10:54 AM
 

Wayne said:

I just read in SQL 2014 BOL that fn_trace_gettable will be removed in a future edition.  Since they didn't say what the alternative was, I'm hoping it isn't deprecated right now.

May 21, 2015 11:30 AM
 

AaronBertrand said:

@Wayne no, you can still use it. "Deprecated" does not mean "no longer exists and can't be used" it just means that at some point in a later version it will be phased out.

May 26, 2015 12:31 PM
 

Shivendra Kumar Yadav said:

Nice! thanks for sharing.

I have a doubt that It gets clear frequently so what is the frequency of it.

Can I get information of a day or something?

September 3, 2015 9:59 AM
 

AaronBertrand said:

@Shivendra There is no way for me to know how long information stays in your default trace - it all depends on how much activity you have that is captured by the trace. Can you get information from a specific day? Sure, as long as that information hasn't been rolled out, you can filter on the StartTime/EndTime columns.

September 14, 2015 12:37 PM
 

Shivendra Kumar Yadav said:

Thanks Aaron,

Yes, I am also experiencing the same that It gets clear any-time.

I have just set SQL profiler with “Data File Auto grow” and “Log File Auto Grow” events since last 7-8 days and using your query with it for analysis.

It has not impacted the CPU consumption too. The file size and its open running to the window is my concern now.

September 15, 2015 8:44 AM
New Comments to this post are disabled

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Privacy Statement