I have a database in which I noticed that one of my tables is missing.

I haven't used this database for over three months and now I want to find out who caused this, when this happened, how it happened and what code was used (or generated by SQLMS) for this action.

How can I do that?

migrated from stackoverflow.com Mar 26 '14 at 19:15

This question came from our site for professional and enthusiast programmers.


This data might still be in the default trace, which can roll over and phase out files based not only on activity but also based on service restarts (so I say it might be there because you may have restarted SQL Server more than 5 or 6 times in three months, even if there hasn't been a lot of activity).

Anyway, it is one of the examples I included in this answer, which I'll repeat a portion of here:

Question: Who deleted the dbo.EmployeeAuditData table and when?

This will return any DROP events for an object named EmployeeAuditData. If you want to make sure that it only detects DROP events for tables, you can add a filter: ObjectType = 8277 (the full list is documented here). If you want to restrict the search space to a specific database, you can add a filter: DatabaseName = N'db_name'.


   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
AND ObjectName = N'EmployeeAuditData'

Please also see the other answer for additional caveats (e.g. details around the lack of recording of the schema of an object that has been dropped, and also the fact that TextData will never be populated - not that it will ever be anything varying far from DROP TABLE dbo.whatever;).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy