I came across a question on ServerFault this afternoon that inflamed my desire to be ultra-geeky (it was really already inflamed after teaching backup and restore internals all afternoon). Basically the question boiled down to how to find out who dropped a table if there’s no other way except the transaction log (e.g. no tracing is available, even the default trace). So I hacked around and figured out at least how to find out *when* a particular table was dropped plus who dropped it.

Everything hinges on using undocumented commands to look into the transaction log. I’ve played with this before on the blog: fn_dblog.

First off I created a script to create a database, populate a table and then drop it.

USE [master];
GO
CREATE DATABASE [FnDbLogTest];
GO
USE [FnDbLogTest];
GO

CREATE TABLE [TestTable] (
[c1] INT IDENTITY,
[c2] CHAR (100) DEFAULT ‘a’);
GO
CREATE CLUSTERED INDEX [TT_CL] ON [TestTable] ([c1]);
GO
INSERT INTO [TestTable] DEFAULT VALUES;
GO

SELECT OBJECT_ID (N’TestTable’);
GO

DROP TABLE [TestTable];
GO

First we need to find the transactions that drop tables in the log:

SELECT [Transaction Id], [Begin Time], SUSER_SNAME ([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N’DROPOBJ’;
GO

The (NULL, NULL) is the starting LSN and ending LSN to process – NULL means process everything available. 

Results are:

Transaction Id Begin Time              User
————– ———————– —————
0000:000000e0  2009/06/16 18:23:03:320 APPLECROSS\Paul

Now, this only shows us that a table was dropped, not which table it was. There’s no way to get the name of the table that was dropped, only the object ID – so you’ll need to have some other way to determine what the table ID is if there are multiple table drops and only one of them is malignant.

This code will give you the object ID of the dropped table:

SELECT TOP (1) [Lock Information] FROM fn_dblog (NULL, NULL)
WHERE [Transaction Id] = ’0000:000000e0′
AND [Lock Information] LIKE ‘%SCH_M OBJECT%’;
GO

Lock Information
————————————————
HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 25:245575913:0

The 25:245575913 is the database ID and object ID of the table that was dropped, and you can look up the object ID in an earlier backup of the database.

Now you can go find whoever it was and take whatever action you deem appropriate :-)

Hope this helps!

PS If you find the you don’t get enough info from fn_dblog, try turning on trace flag 2537. It allows the function to look at *all* possible log, not just the active log.