Day 19: who drop/detached Database/object

One day we found that user has reported that we should have one database “XXX” to be on the server, we checked the error log and found that the database “XXX” is getting restored daily. But when we check on the server we could not see the database exists on the server.

Means we could detect that someone is restoring the database but database is not exists. Error log keeps record of restore only, to find out how the database is dropped or detached we need to use the default trace, which is introduced in sql server 2005 and enabled by default.

This trace flag keeps little information about the database activity and creates .trc file at the default location where your Error log file exists can be get path from table sys.traces and id =1 means default trace, and to read the trace file use function fn_trace_gettable, its good practice to insert the trace data into temp table to manipulate the output per requirement.

The standard script would be

DECLARE @path varchar(256) 

SELECT @path = path FROM sys.traces where id = 1 

SELECT * into #defaultT FROM fn_trace_gettable(@path, 1)

Select textdata,starttime,targetusername from #defaultT

Analyse the data where you can find who/when the data is detached or deleted

http://stackoverflow.com/questions/1043971/determine-which-user-deleted-a-sql-server-database

and to know the object we need to read the transaction log

SELECT [Transaction Id], [Begin Time], [UID], [SPID]
FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Name] = ‘DROPOBJ’
GO

 

http://sqlskills.com/BLOGS/PAUL/post/Finding-out-who-dropped-a-table-using-the-transaction-log.aspx

[Update 03 Dec 2012]:

Paul has written another blog on how to find the same if tlog is inactive using another undocumented function fn_dump_dblog how to concert LSN format.

http://www.sqlskills.com/blogs/paul/default.aspx?page=2

other good alternative is pre-planning using

DDL Trigger would be another good option.

http://msdn.microsoft.com/en-us/library/ms186406.aspx

Advertisements
This entry was posted in Troubleshooting and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s