Who Shrank Roger Rabbit

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.

USE test
DBCC LOG ('test')

No dice. I tried master and msdb too!

DBCC LOG ('master')
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;
EXEC master.dbo.SP_CONFIGURE 'default trace enabled';

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.

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

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

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

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.

	  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
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
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!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *