Who Dropped The Database?

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

Leave a Reply

Your email address will not be published.