A database has been changed into simple recovery, and the CIO wants to know who did it.
If you want to play along on your test instance run this command:
ALTER DATABASE <your_full_recovery_db> SET RECOVERY SIMPLE;
There are two main sources to mine for information: the SQL error log and the default trace.
SQL Error Log:
The command to use in this instance is:
Parameter 1 is the file to use – 0 is the current error log. How many historical logs are available depends on how SQL Server is configured – however in this case because we are looking at a fresh trail we will be okay the current log.
Parameter 2 specifies which whether to use the SQL Server or SQL Agent error log – 1 is SQL Server, 2 is SQL Agent.
Parameter 3 & 4 are search strings. You don’t have to use two search strings but if you do, xp_readerrorlog will return rows from the log that contain both search strings in the message column.
Running this in my test environment gives:
So far so good. We have the SPID that made the change and the time the change was made – SPID 59 in my case. Time to look at the trace.
The default trace:
To check whether the default trace is running run sp_configure and look for default trace enabled (it should be 1).
In order to locate the trace file run the following:
select * from ::fn_trace_getinfo(default)
This will return the path and filename of the active trace which can then be queried with:
select * from ::fn_trace_gettable('<path + filename>',default)
The default parameter will cause the rolled trace files to be loaded also. How much remains in the trace files will depend on how busy the server is and how quickly you get to it.
Now to get more useful we need to join this table to sys.trace_events and then filter down to our culprit, using the SPID from the error log. To see the various event categories and associated ids look here. The one we are after is 164 which is Object:Altered.
Here is the query with join and filter:
select e.name , t.HostName , t.ApplicationName , t.LoginName , t.StartTime ,t.SPID from ::fn_trace_gettable('<pathname + filename>',default) t join sys.trace_events e on t.EventClass = e.trace_event_id where e.trace_event_id = 164 and t.SPID = 59
And it looks like we have the culprit:
Don’t forget to put your database back into full recovery!
ALTER DATABASE <your_db> SET RECOVERY FULL;