Skip to content

Who Shrank Roger Rabbit

2011 November 21
by Shannon Lowder

It all started Thursday afternoon, about 1500. I was walking back from Starbucks with my afternoon pick-me-up, when a co-worker mentioned we have this process or person who shrinks the log file, apparently every week. “Is there some way I can find out who, or what is shrinking the log files?”

And with that, I started digging.

I knew you could set up a trace to see when it’s run, but I needed to look at a shrink operation that had already run. I couldn’t remember if that was a logged operation, so I opened up my local test instance, created a database, then shrank the log file. And checked out DBCC LOG.

CREATE DATABASE test
GO
USE test
GO
DBCC SHRINKFILE (NAME='test_log')
GO
DBCC LOG ('test')
GO

No dice. I tried master and msdb too!

DBCC LOG ('master')
GO
DBCC LOG ('msdb')

OK, so I thought I’d look in the event log.

hit the start button and start typing "event viewer"

Nothing. Well… let’s see what google says about looking for DBCC operations in a log file. After several tries, nothing useful. Ok, let’s try #SQLhelp.

#SQLHelp, where is DBCC SHRINKFILE logged? Event log, or in a transaction log?

Since I didn’t mention I wanted to look at an event that had already happened I immediately got the response to set up a trace using Profiler.

I also got a suggestion to install a third party tool. I didn’t mention that installing a third party tool would be a very time consuming process with the formal change controls I have to work through.

Then I was told to check out the default trace. I did a quick check in Books Online. Not a lot of information. Then I found a TechNet blog entry.
So let’s check to see if the default trace is still on in this instance. Since I’m in a pretty strict environment, it could be turned off.

EXEC master.dbo.SP_CONFIGURE 'show advanced options', 1;
GO
EXEC master.dbo.SP_CONFIGURE 'default trace enabled';
GO

Config_value is 1, run_value is 1. Looks like it’s running. Let’s find the filename for the default trace.

SELECT *   FROM ::fn_trace_getinfo(default)

Property 2 is my filename, copy the filename, now we can use the built in SQL function, FN_TRACE_GETTABLE, to turn the trace into a table.

SELECT
      StartTime
    , EndTime
    , TextData
    , DatabaseName
    , HostName
    , ApplicationName
    , LoginName
FROM FN_TRACE_GETTABLE('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_5.trc', DEFAULT)
WHERE TextData LIKE '%SHRINKFILE%';

Hrm… no rows. But I know the database is getting it’s log file shrunk. Let’s try something a little different.

SELECT
      StartTime
    , EndTime
    , TextData
    , DatabaseName
    , HostName
    , ApplicationName
    , LoginName
FROM FN_TRACE_GETTABLE('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_5.trc', DEFAULT)
WHERE TextData LIKE '%SHRINK%';

Oh, ho! It’s a SHRINKDATBASE operation! And there is the userid, and time of the event! Now, let’s go stop that process from doing this again. Google “Paul Randal shrink” if you want to know why I’m stopping that process from happening automatically every week.

Since the default trace isn’t very well documented check out what events are captured in your default trace. I thought I’d try and find out on my own machine. I don’t know for sure if my list of events has been edited, I don’t know what changes have been made to my server. You can run the following code on your machine to see your list of captured events.

SELECT DISTINCT
	  te.name as EventsCaptured
FROM ::fn_trace_geteventinfo(1) t
INNER JOIN sys.trace_events te
	ON t.eventID = te.trace_event_id
INNER JOIN sys.trace_columns tc
	ON t.columnid = tc.trace_column_id

On my machine I have the following audits turned on.

Audit Add DB User Event
Audit Add Login to Server Role Event
Audit Add Member to DB Role Event
Audit Add Role Event
Audit Addlogin Event
Audit Backup/Restore Event
Audit Change Audit Event
Audit Change Database Owner
Audit Database Scope GDR Event
Audit DBCC Event
Audit Login Change Property Event
Audit Login Failed
Audit Login GDR Event
Audit Schema Object GDR Event
Audit Schema Object Take Ownership Event
Audit Server Alter Trace Event
Audit Server Starts And Stops
Data File Auto Grow
Data File Auto Shrink
Database Mirroring State Change
ErrorLog
FT:Crawl Aborted
FT:Crawl Started
FT:Crawl Stopped
Hash Warning
Log File Auto Grow
Log File Auto Shrink
Missing Column Statistics
Missing Join Predicate
Object:Altered
Object:Created
Object:Deleted
Plan Guide Unsuccessful
Server Memory Change
Sort Warnings

So, I was able to answer the question with a little digging, and some #SQLhelp! This a fun little excersize to figure out what was causing us problems. If you’re having SQL problems, let me know. I’ll do what I can to help!

No comments yet

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS