Who Dropped my database?

I borrowed the starter of this effort from SQL Server Expert and MVP Pinal Dave here: SQL SERVER – Who Dropped Table or Database?

We have a staging server that has a database for each client that we can test on before working in their production databases.   Recently one has gone missing a few times…but nobody will own up to it.

For a quick hit, I went to Pinal’s site and found most of the below code to read through all of the default trace files on the system and return the Object Dropped, Altered or Created Event Classes.

I have since modified it with a few comments, readability, commented reference links, etc.

I do not claim this work as my own (just the modifications), nor do I promise it will work in your environment.  Learn it, understand it, then use it…in that order 🙂

-- read all available traces.
-- including current
DECLARE @current VARCHAR(500);
DECLARE @start VARCHAR(500);
DECLARE @indx INT;

SELECT @current = path
FROM sys.traces
WHERE is_default = 1;

--remove the rollover number from the filename
--(i.e., log_157.trc becomes log.trc)
SET @current = REVERSE(@current)
SELECT @indx = PATINDEX('%\%', @current)
SET @current = REVERSE(@current)
SET @start = LEFT(@current, LEN(@current) - @indx) + '\log.trc';

-- CHANGE FILTER AS NEEDED
SELECT
    CASE EventClass
      WHEN 46 THEN 'Object:Created'
      WHEN 47 THEN 'Object:Deleted'
      WHEN 164 THEN 'Object:Altered'
      ELSE 'Other'
END as EventClass,
    DatabaseName,
    ObjectName,
    ObjectType, -- https://bit.ly/2hDOJ8V
    HostName,
    ApplicationName,
    LoginName,
    StartTime
FROM
    ::fn_trace_gettable(@start, DEFAULT)
WHERE 1=1
    and EventClass IN 
    (
    46,
    47,
    164
    ) --set events: https://bit.ly/2hMDz2r
    AND EventSubclass = 0
    AND DatabaseID <> 2 --eliminate tempdb
    --AND DatabaseName Like '%%'
ORDER BY StartTime DESC

As a test, I created and dropped a ‘test’ database, then ran the above:

I am confident that the next time this DB goes away, I will find out who and how…maybe even why.   I can also run this for pretty much anything else just by modifying the Event Classes in the WHERE clause.

Happy hunting and thanks for reading!

Kevin3NF