Where is My Database

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:

  1. Who did it?

  2. 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:

  1. It is enabled by default on any new instance, and you can disable it using the advanced server option “default trace enabled”.

  2. It writes the data to files in the default instance log folder (which should be something like “C:Program FilesMicrosoft SQL ServerMSSQL11.SERVERNAMEMSSQLLog”).

  3. It uses a maximum file size of 20MB and 5 rollover files.

  4. Every time the instance is restarted, a new trace file is created.

  5. 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.HostNa