I don’t know how many of you know about the default trace that is running in SQL Server 2008 and 2005, but it can be very useful tool to track down changes.  This trace should be running by default unless some error has taken place on the instance of SQL Server or if the option has been set to off.  (It is on by default no kidding!)  This trace tracks a number of different events but mainly looks a events that deal with changes to the database either caused specifically by the user like DBCC commands or  create table or similar DDL statements or automatic changes like log growths.

Trace Event tracked

Audit Add DB User Event (event id:109)
Audit Add Login to Server Role Event (event id:108)
Audit Add Member to DB Role Event (event id:110)
Audit Add Role Event (event id:111)
Audit Addlogin Event (event id:104)
Audit Backup/Restore Event (event id:115)
Audit Change Audit Event (event id:117)
Audit Change Database Owner (event id:152)
Audit Database Scope GDR Event (event id:102)
Audit DBCC Event (event id:116)
Audit Login Change Property Event (event id:106)
Audit Login Failed (event id:20 )
Audit Login GDR Event (event id:105)
Audit Schema Object GDR Event (event id:103)
Audit Schema Object Take Ownership Event (event id:153)
Audit Server Alter Trace Event (event id:175)
Audit Server Starts And Stops (event id:18 )
Data File Auto Grow (event id:92 )
Data File Auto Shrink (event id:94 )
Database Mirroring State Change (event id:167)
ErrorLog (event id:22 )
FT:Crawl Aborted (event id:157)
FT:Crawl Started (event id:155)
FT:Crawl Stopped (event id:156)
Hash Warning (event id:55 )
Log File Auto Grow (event id:93 )
Log File Auto Shrink (event id:95 )
Missing Column Statistics (event id:79 )
Missing Join Predicate (event id:80 )
Object:Altered (event id:164)
Object:Created (event id:46 )
Object:Deleted (event id:47 )
Plan Guide Unsuccessful (event id:218)
Server Memory Change (event id:81 )
Sort Warnings (event id:69 )
The other day I found a need to quickly look at the log growths of a number of different databases.  While I could have right clicked each database one by one and selected Report then select database size and run the report in SSMS I wrote a quick script.  I found it pretty useful to write a quick script and read it all at once from the trace itself. This script may even find its way into the UtiltityDB as a in-line table function.  The “integerData” column can represent different numeric data points depending on what event you are looking at, when you look at the log growth event the value is the number of 8k pages the log grew.

Log Growth Script

SELECT
databaseid
,ntdomainname
,hostname
,clientprocessid
,applicationname
,loginname
,spid
,duration
,starttime
,endtime
,integerdata [Pages]
,servername
,eventclass
,databasename
,filename
,loginsid
,eventsequence
,sessionloginname
FROM
Fn_trace_gettable('E:\MSSQL10.MSSQLSERVER\MSSQL\Log\log.trc',DEFAULT) AS fng
WHERE
fng.eventclass=93
ORDER BY starttime DESC

That’s it short and sweet.  Let me know if you find another good use for the default trace!