Once upon a time there was a database. The database was perfectly designed with beautiful tables and views accompanied by good-looking stored procedures and outstanding indexes. People loved this database and used it with care. They fed it, secured it, and kept it clean and safe. And then, one day, it was gone!
This fairytale happened to me once, when one of our customers called me in panic and told me that their database is gone…
“What do you mean – it’s gone?” – I asked. “Gone, vanished, disappeared, got lost…” – he replied. “How the hell does a database get lost?” – I asked. “I don’t know! Can you stop asking annoying questions and do something about it?”
After a short investigation, which didn’t conclude anything, we restored the database from backup, and everything went back to normal, more or less. But the customer was still unhappy. He wanted to know the answers to two simple questions:
Who did it?
When exactly did it happen?
Simple (and justified) questions, indeed, but it turns out that the answers are not so simple…
“Do you have any audit in place?” – I asked. “No” – he replied. “Are you sure? SQL Server Audit, DDL Triggers, anything?” – I tried… “No” – he insisted. “Never mind, it’s probably written in the SQL Server log. I’ll find it there…” – I said.
I was pretty sure that an event of dropping a database would be written to the SQL Server log, but I was surprised to find out that it isn’t. Every time you back up your database, an event is written to the log (by default), but when the database is dropped – nothing. Same goes for the Windows Application Log, of course. For some reason, Microsoft believes that a database backup is more important to audit than a database removal…
So I had to tell the customer that I was wrong and to find another place to look for answers to his two simple questions. Then I remembered that there is a default Extended Events session called system_health that includes some useful events. I assumed that it wasn’t disabled by the customer, and I was happy to find out that I was right. So I started digging into the events included in the “system_health” session. I found that this session includes interesting events like deadlocks and errors with a high severity, but unfortunately it has nothing to do with dropping a database.
At this point I started to lose hope. But then I remembered that just like there’s a default event session, there’s also a default trace. And I was happy to discover that it was also running on my customer’s server. Unlike the default event session, the default trace contains much more events. This is a bit surprising, taking into account that Microsoft states that “this feature (SQL Trace) will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Events instead”.
A few facts about the default trace:
It is enabled by default on any new instance, and you can disable it using the advanced server option “default trace enabled”.
It writes the data to files in the default instance log folder (which should be something like “C:Program FilesMicrosoft SQL ServerMSSQL11.SERVERNAMEMSSQLLog”).
It uses a maximum file size of 20MB and 5 rollover files.
Every time the instance is restarted, a new trace file is created.
You can’t modify its settings.
You can check the default trace settings by running the following query:
Transact-SQL
SELECT * FROM sys.fn_trace_getinfo (1);
1
2
3
4
SELECT
*
FROM
sys.fn_trace_getinfo (1);
Here are the results on my computer:
You can query the information in the trace files like this:
Transact-SQL
SELECT EventCategroyName = TraceCategories.name , EventName = TraceEvents.name , EventSubClass = TraceData.EventSubClass , HostName = TraceData.HostName , ApplicationName = TraceData.ApplicationName , ServerName = TraceData.ServerName , DatabaseName = TraceData.DatabaseName , ObjectName = TraceData.ObjectName , SessionId = TraceData.SPID , LoginName = TraceData.LoginName , TextData = TraceData.TextData , StartTime = TraceData.StartTime FROM sys.fn_trace_gettable (N'Trace File Name' , DEFAULT) AS TraceData INNER JOIN sys.trace_events AS TraceEvents ON TraceData.EventClass = TraceEvents.trace_event_id INNER JOIN sys.trace_categories AS TraceCategories ON TraceEvents.category_id = TraceCategories.category_id ORDER BY TraceData.StartTime ASC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT
EventCategroyName = TraceCategories.name ,
EventName = TraceEvents.name ,
EventSubClass = TraceData.EventSubClass ,
HostName = TraceData.HostName ,
ApplicationName = TraceData.ApplicationName ,
ServerName = TraceData.ServerName ,
DatabaseName = TraceData.DatabaseName ,
ObjectName = TraceData.ObjectName ,
SessionId = TraceData.SPID ,
LoginName = TraceData.LoginName ,
TextData = TraceData.TextData ,
StartTime = TraceData.StartTime
FROM
sys.fn_trace_gettable (N'Trace File Name' , DEFAULT) AS TraceData
INNER JOIN
sys.trace_events AS TraceEvents
ON
TraceData.EventClass = TraceEvents.trace_event_id
INNER JOIN
sys.trace_categories AS TraceCategories
ON
TraceEvents.category_id = TraceCategories.category_id
ORDER BY
TraceData.StartTime ASC;
It turns out that there are a handful of events in there. One of the events is “Object:Deleted” (event ID = 47). It captures any event of dropping an object anywhere in the instance. The good news is that it covers not only database objects but also the databases themselves.
So here is the query that provided the answers to my customer’s questions:
Transact-SQL
SELECT HostName = HostName , ApplicationName = ApplicationName , SessionId = SPID , LoginName = LoginName , StartTime = StartTime , FROM sys.fn_trace_gettable (N'Trace File Name' , DEFAULT) AS TraceData WHERE DatabaseName = N'DatabaseName' AND ObjectID IS NULL AND EventClass = 47 AND EventSubClass = 1 ORDER BY StartTime ASC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
HostName = HostName ,
ApplicationName = ApplicationName ,
SessionId = SPID ,
LoginName = LoginName ,
StartTime = StartTime ,
FROM
sys.fn_trace_gettable (N'Trace File Name' , DEFAULT) AS TraceData
WHERE
DatabaseName = N'DatabaseName'
AND
ObjectID IS NULL
AND
EventClass = 47
AND
EventSubClass = 1
ORDER BY
StartTime ASC;
Actually, it’s even much simpler than that. In SSMS, you can right-click on the instance in the object explorer, and then choose “Reports -> Standard Reports -> Schema Changes History”. This built-in report displays all the DDL statement executions reported by the default trace. Nice and easy!
Want to know who dropped the database? It was one of the developers who thought he was “playing” with the dev environment, while he was actually connected to the production server. The poor guy wasn’t even aware of the crisis until the DB police came in and arrested him…