Occasionally I find it necessary to determine the Who and When of a database being created or deleted on one of our dev servers. As long as it was done recently, the information is readily available in the default trace log and can be extracted using sys.fn_trace_gettable. Here’s an easy query that returns the database create/delete information from the current trace log.
IF (SELECT value_in_use FROM sys.configurations WHERE NAME = 'default trace enabled') = 1 BEGIN DECLARE @intpara INT; DECLARE @CurrentTrace VARCHAR(500); DECLARE @BaseTrace VARCHAR(500); SET @CurrentTrace = (SELECT REVERSE([path]) FROM sys.traces WHERE is_default = 1); SELECT @intpara = PATINDEX('%\%' , @CurrentTrace); SET @CurrentTrace = REVERSE(@CurrentTrace); SET @BaseTrace = LEFT( @CurrentTrace ,LEN(@CurrentTrace) - @intpara) + '\log.trc'; SELECT DatabaseName ,StartTime ,HostName ,LoginName ,ApplicationName ,TE.name AS EventName FROM ::fn_trace_gettable( @BaseTrace , DEFAULT ) TT INNER JOIN sys.trace_events TE ON TT.EventClass = TE.trace_event_id WHERE EventClass IN (46,47) AND ObjectType = 16964 AND EventSubClass = 1 ORDER BY StartTime DESC; END
