snippetModerate

How can I determine who dropped a DB in SQL Server 2005 (and when)?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

candroppedwhosqland2005determinehowserverwhen

Problem

Is there a way to find out who dropped a database in SQL Server 2005 and the time / date that it was dropped?

Solution

If it was recent enough, it may still be in the default trace. The basic assumption will be that the DROP event will be the last event recorded for this database. So if someone dropped the database 'splunge' you should be able to catch it this way (again depending on how long ago it happened):

DECLARE @FileName VARCHAR(255)  

SELECT @FileName = SUBSTRING(path, 0, 
    LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  --'
  FROM sys.traces WHERE is_default = 1;  

SELECT TOP 1 LoginName, HostName, ApplicationName, StartTime, TextData
  FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt  
  WHERE EventClass = 47 AND DatabaseName = 'splunge'
  ORDER BY StartTime DESC;

Code Snippets

DECLARE @FileName VARCHAR(255)  

SELECT @FileName = SUBSTRING(path, 0, 
    LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  --'
  FROM sys.traces WHERE is_default = 1;  

SELECT TOP 1 LoginName, HostName, ApplicationName, StartTime, TextData
  FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt  
  WHERE EventClass = 47 AND DatabaseName = 'splunge'
  ORDER BY StartTime DESC;

Context

StackExchange Database Administrators Q#14186, answer score: 10

Revisions (0)

No revisions yet.