So, back to our example - what changed? The first thing I would recommend is to query sys.trace_events and perhaps paste the results into Excel, remove the ones you likely won't use often and print it out as a quick reference. After all, time is of the essence when you are troubleshooting live issues.
Here is the query I used to create my quick reference:
FROM sys.trace_events te
INNER JOIN sys.trace_categories tc ON te.category_id = tc.category_id
ORDER BY tc.[name]
So, in this particular case you want to focus on the following subset of events:
Next, you want to pull up a query that will go through all five default traces, filtering for the events you are interested in. I recently wrote a post on how to query all five default traces at once, so I won't go into that here.
Here is the query I use (I know it's in the Test database, so I'll filter on that also):
DECLARE @filename NVARCHAR(4000);
-- Current default trace
SELECT @filename = CAST(value AS NVARCHAR(4000))
WHERE traceid = 1
AND property = 2;
SET @filename = LEFT(@filename,LEN(@filename) - CHARINDEX('\',REVERSE(@filename))) + '\log.trc'
-- Find auto growth events in the current trace file
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,'Developer' AS NTUserName
FN_TRACE_GETTABLE(@filename,DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE DB_NAME(ftg.databaseid) = 'Test'
AND te.category_id = 5 -- Objects
ORDER BY ftg.StartTime DESC
And... what have we found? A developer, using SSMS, has altered an object called DarnDeveloper in the Test database this morning at around 10am!
I hope this helps you with your forensic analysis and investigative work!