Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 26 at 19:15

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

1 Answer 1

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'.

DECLARE @path NVARCHAR(260);

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

SELECT 
  LoginName,
  HostName,
  StartTime,
  ApplicationName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
AND ObjectName = N'EmployeeAuditData'
ORDER BY StartTime DESC;

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;).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.