THE SQL Server Blog Spot on the Web

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

Tibor Karaszi

Did we have recent autogrow?

I just read the question how to find out when autogrow in tempdb happened. And again I want to push for the default trace. The simple answer is that you already have this information available, just go and get it! (Unless you explicitly disabled the default trace, of course...)

Adjust the file name for your most recent default trace file in below query:

SELECT te.namet.DatabaseNamet.FileNamet.StartTimet.ApplicatioNname
FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\LOG\log_331.trc', NULL) AS t
INNER JOIN sys.trace_events AS te ON t.EventClass te.trace_event_id
WHERE te.name LIKE '%Auto Grow'
ORDER BY StartTime ASC
Published Thursday, June 19, 2008 4:51 PM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

Hey Tibor, I wrote up a very similar query, on this very site, last January.  :-)

http://sqlblog.com/blogs/aaron_bertrand/archive/2007/01/11/reviewing-autogrow-events-from-the-default-trace.aspx

June 19, 2008 9:49 PM
 

TiborKaraszi said:

Hi Aaron!

Hmm... Note to self: Search blog before writing. :-)

Btw, did you consider getting the actual default trace file name from sys.traces? Something like:

DECLARE @fn VARCHAR(1000), @df bit

SELECT @fn = "path" @df = is_default FROM sys.traces WHERE id = 1

IF @df = 0 OR @df IS NULL

BEGIN

  RAISERROR('No defalt trace running!', 16, 1)

  RETURN

END

SELECT te.name, t.DatabaseName, t.FileName, t.StartTime, t.ApplicatioNname  

FROM fn_trace_gettable(@fn, DEFAULT) AS t  

INNER JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id  

WHERE te.name LIKE '%Auto Grow'  

ORDER BY StartTime ASC    

June 20, 2008 3:02 AM
 

AaronBertrand said:

That's an easier way to get the path, for sure.  However by storing a single value in a variable you are just picking the most recent default trace file, which may have just been rolled into 5 minutes ago... so you would miss autogrow events that happened 10 minutes ago, or yesterday, or the minute the earliest .trc file was created.

June 20, 2008 11:25 AM
 

Aaron Bertrand : Reviewing AutoGrow events from the default trace said:

June 20, 2008 11:31 AM
 

TiborKaraszi said:

Hi again Aaron!

Hmm, yes. It seems I got the "read-rollover-files" parameter backwards. I figured we read the most recent trace file from sys.traces and by specifying default for the 2:nd param of fn_trace_gettable, then all the older files would be read as well.

But I was thinking backwards. You need to specify the *first* (low) file for the param and it can automatically read files with higher values...

June 21, 2008 4:12 AM
 

ALZDBA said:

I discovered it after installing PerformanceDashboard !

It's in there, and it comes quit handy (at least if you're on SP2 or higher)

June 21, 2008 5:59 PM
 

basmin said:

thanks for sharing the nice code.

this very useful

August 2, 2009 11:47 AM
 

Arthur said:

Hey, thanks for this query. Quite useful!

April 8, 2010 8:46 AM
 

Nick Duckstein said:

I would think the autogrowth information queryable from the database. The default trace is only from the last time the SQL Service was restarted. We have clients that have 1 TB files and we'ld like to know if the database grew in 1MB increments (default autogrowth was 1MB). There would be 1 Million autogrowths in that scenario. That history would be over the last couple years and not available in the default trace.

Thanks,

Nick

September 30, 2010 5:04 PM
 

TiborKaraszi said:

@Nick: default trace is not only since last re-start. There are 5 trace files, limited to 20MB each. So, it can be as much as 5 re-starts, but each file is limited to 20MB (wll be aged out when becomes 20MB, still llimited to 5 files). If you need more, then you need to track it yourself. Not automatically tracked in db...

September 30, 2010 5:24 PM
 

Terry Grignon said:

Thanks Tibor and Aaron. Your query in the notes is very useful as I can't use Aaron's query without turning on xp_cmdshell which is a security issue where I work. You'd think that autogrow events (holy important, Batman!) would be stored in DMV's and more accessible.

September 22, 2011 1:40 PM
 

Mike said:

Hey Tibor,  is it possible to get an email or run a stored procedure at the time of a auto_grow.  Maybe something like a trigger to alert the DBA a auto grow just toook place?

February 22, 2012 2:12 PM
 

TiborKaraszi said:

Mike,

How about using Event Notifications, going to a queue on which you (if you wish) have defined Activation?

February 23, 2012 1:00 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement