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!