What do you do when a developer comes to you and asks, “Where did the database go? The database was there one minute, and the next it was not.” Only one thing could be worse than the feeling of losing a database on your watch, and that would be losing a production database. It’s like magic—it’s there, and then it disappears. To compound the issue, when asking people if they know what might have happened, all will typically deny, deny, deny.
What do you do when you run into that missing database situation and the inevitable denial that will ensue? This is when an audit can save the day. Through an audit, you can discover who dropped the database and when it happened. Then you have hard data to take back to the team to again ask what happened. Taking the info from a previous article of mine, we can alter the script I published there and re-use it for our needs here.
DECLARE @DBName sysname = 'AdventureWorks2014'
,@d1 DATETIME
,@diff INT;
SELECT ObjectName
, ObjectID
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, NTUserName
, ApplicationName
, CASE EventClass
WHEN 46
THEN 'CREATE'
WHEN 47
THEN 'DROP'
--WHEN 164
-- THEN 'ALTER'
END AS DDLOperation
INTO #temp_trace
FROM sys.FN_TRACE_GETTABLE(CONVERT(VARCHAR(150),
( SELECT REVERSE(SUBSTRING(REVERSE(PATH),
CHARINDEX('\',REVERSE(path)),256)) + 'LOG.trc'
FROM sys.traces
WHERE is_default = 1)), DEFAULT) T
WHERE EventClass in (46,47)
AND EventSubclass = 0
AND ObjectType = 16964-- i just want database related events
AND DatabaseName = ISNULL(@DBName,DatabaseName);
SELECT @d1 = MIN(StartTime)
FROM #temp_trace;
SET @diff= DATEDIFF(hh,@d1,GETDATE());
SELECT @diff AS HrsSinceFirstChange
, @d1 AS FirstChangeDate
, sv.name AS obj_type_desc
, tt.ObjectType
, tt.DDLOperation
, tt.DatabaseName,tt.ObjectName,tt.StartTime
, tt.EventClass,tt.EventSubClass
, tt.ServerName,tt.LoginName, tt.NTUserName
, tt.ApplicationName
, (dense_rank() OVER (ORDER BY ObjectName,ObjectType ) )%2 AS l1
, (dense_rank() OVER (ORDER BY ObjectName,ObjectType,StartTime ))%2 AS l2
FROM #temp_trace tt
INNER JOIN sys.trace_events AS te
ON tt.EventClass = te.trace_event_id
INNER JOIN sys.trace_subclass_values tsv
ON tt.EventClass = tsv.trace_event_id
AND tt.ObjectType = tsv.subclass_value
INNER JOIN master.dbo.spt_values sv
ON tsv.subclass_value = sv.number
AND sv.type = 'EOD'
ORDER BY StartTime DESC;
DROP TABLE #temp_trace;This script will now query the default trace to determine when a database was dropped or created. I am limiting this result set through the use of this filter: ObjectType = 16964. In addition to that, I have also trimmed the result-set down to just look for drop or create events.
This is the type of information that is already available within the default trace. What if you wished to not be entirely dependent on the default trace for that information? As luck would have it, you don’t need to be solely dependent on the default trace. Instead you can use the robust tool called extended events.
