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:

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:

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…

#trace #monitoring #databaseadministration #extendedevents #auditing #databaseadministrationservice #log