THE SQL Server Blog Spot on the Web

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

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

When was the last time a Database or file was shrunk?

Just for starters, I don't need a primer on how shrinking a database is bad, or why I shouldn't be doing it.  Trust me, I understand the implications of shrinking databases, why it shouldn't be done, and I explain this to people online all the time.  That being said, this question came to me from a vendor of ours, and I am not sure of the context of the need because I didn't ask, but I'd imagine that he is trying to prove that shrinking is being performed in one of his support cases, and since I like to help people out when they ask for it, so I figured I'd give it a shot.

So where would you look for this kind of information?  I wasn't sure where to start to be honest, so I started by shrinking one of my development databases on a development server.  Checked the ErrorLog, and found nothing, then through some DMV's and found nothing, then I checked the Database using DBCC PAGE and again found nothing, so I hit up twitter and asked if this could even be found, and got a quick response from Paul Randal letting me know it isn't tracked anywhere in the database, so probably not. 

After letting the person who asked me about this know that, and trading a few emails on the subject, it dawned on me that the default trace might be able to tell this information if it hasn't rolled over yet, so I put together a quick test script and Voila, I had found what I was looking for:

DECLARE @filename VARCHAR(MAX)
SELECT @filename = CAST(value AS VARCHAR(MAX))
FROM fn_trace_getinfo(DEFAULT)
WHERE property = 2
 
AND value IS NOT NULL

SELECT gt.EventClass,
      
te.Name AS EventName
      
gt.TEXTData,
      
gt.NTUserName,
      
gt.NTDomainName,
      
gt.HostName,
      
gt.ApplicationName,
      
gt.LoginName,
      
gt.SPID,
      
gt.StartTime,
      
gt.EndTime,
      
gt.ObjectName,
      
gt.DatabaseName,
      
gt.FileName
FROM [fn_trace_gettable](@fileName, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass = 116
 
AND TEXTData LIKE '%SHRINK%'
ORDER BY StartTime;

Ok, so now feel free to comment about how bad shrinking databases is, and why I shouldn't do it.  I can take it.


Published Wednesday, May 13, 2009 1:52 PM by Jonathan Kehayias
Filed under:

Comments

 

Ben Nevarez said:

Hi Jonathan,

I think the default trace has been available all the time since SQL Server 2005 RTM.

Regards,

Ben

May 13, 2009 2:44 PM
 

Jonathan Kehayias said:

Ben,

You are probably correct, so I will update this post and remove that note about SP2.  I am probably confusing it with something else that was included in SP2.

May 13, 2009 3:04 PM
 

Sankar Reddy said:

Good post Jonathan. Default trace & BPA are invaluable tools in any DBA arsenal.

May 13, 2009 4:11 PM
 

Scott R. said:

Jonathan,

Great idea!  I really like this concept of querying the active default trace.

I ran into a few issues that caused me to explore further.  Using the query as provided, I didn't find any shrink events on a DB instance setup to demo such events.  Looking further at the sys.trace_event table you joined to in the query, I found two event codes that may be a more appropriate filter:

-  94 Data File Auto Shrink

-  95 Log File Auto Shrink

By changing the Where clause to:

Where EventClass In (94, 95)

I can now see auto-shrink events.

FYI – If you care to look for them, auto grow event class IDs are:

-  92 Data File Auto Grow

-  93 Log File Auto Grow

Reviewing such information from SQL Profiler is good, but these queries can be used in many more situations (batch scripts, scheduled jobs, etc.) where you might not want to or can’t use SQL Profiler.

Thanks for revealing another great information source.

Scott R.

May 13, 2009 5:45 PM
 

Jonathan Kehayias said:

Scott,

I initially looked at those two events, but I was specifically looking for shrinks that were initiated manually, or as a part of a database maintenance plan which aren't logged as Auto Shrink Events.  I almost gave up on using the Default Trace until I checked the DBCC events which is where the shrinks I was targeting are actually being logged.  If you have Autoshrink turned on, you should definitely turn it off.  

http://www.sqlskills.com/BLOGS/PAUL/post/Auto-shrink-e28093-turn-it-OFF!.aspx

http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx

http://sqlbatman.com/2009/03/when-to-use-auto-shrink/

It sounds to me like this is just a demo you setup, but just to cover the bases....

You can actually grab a whole lot of information using the DBCC Event class.  The power of the data stored in the default trace files is pretty amazing if you actually dig into them a bit.

May 13, 2009 5:56 PM
 

AaronBertrand said:

I wrote a little about this here on this site a couple of years ago, but for growth events, not shrink events (essentially the approach is identical):

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

Good catch though on event 116, not one people would generally think to look for...

May 16, 2009 3:52 PM
 

David Benoit said:

Jonathan,

Just a note but the initial query to get the filename isn't guaranteed to work if there is another trace running that is dumping to file. The following is another way of getting the filename and is more consistent. Just a thought.

DECLARE @filename VARCHAR(MAX)

SELECT @filename = CAST(path AS VARCHAR(MAX))

FROM sys.traces

WHERE is_default = 1

David

May 27, 2009 2:54 PM
Anonymous comments are disabled

This Blog

Syndication

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