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?

This blog has moved! You can find this content at the following new location:

http://sqlblog.karaszi.com/did-we-have-recent-autogrow/

Published Thursday, June 19, 2008 4:51 PM by TiborKaraszi

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
 

Wayne said:

I just read in SQL 2014 BOL that fn_trace_gettable is planned on being removed in a future edition, they didn't say what the recommended replacement was so I'm hoping that it isn't deprecated yet.

May 21, 2015 11:24 AM
 

TiborKaraszi said:

Correct, Wayne. SQL Trace, including Profiler, is deprecated. Deprecated is just MS telling us it *will* be removed, and we'll see when it is actually removed. I haven't looked at the default Extended Event session which is running, perhaps these events are already in there. Anyhow, we'll see what option we have when SQL Trace is actually removed - whether it will be 2016, 2018 or 2020 (?). :-)

May 21, 2015 1:32 PM
New Comments to this post are disabled
Privacy Statement