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
