Simple way to find who Truncated or Dropped the Table

/*Most Challenging task sometimes would be to figure who dropped the table or who truncated the table on SQL Server database.

So how do we find who performed this operation and when.

Among the Undocumented Functions there are two functions you need to know that might give you some insight.

1.fn_dblog ()

2.fn_dump_dblog ()

The function fn_dblog () works only when transaction log entries have not been purged from the transaction log file (either by CHECKPOINT in Simple recovery model or by the T-Log backup in Full recovery model).

In case the transaction log entries has been purged from the log file because a transaction log backup has been taken after the truncate occurred, we can still use the function fn_dump_dblog() to read the log backup file to get our information.



If you have simple recovery mode on the database, then the Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 20 milliseconds. And so if your'e lucky enough you can get the information before it does the checkpoint.

If you have Full Recovery mode,The logs flushed into disk everytime Log Truncates. So either you can recover the information before the log truncates or else your better of by restoring the Full Backup\T Logs in a different environment and then getting the necessary information.*/


--Below is the working example of how it works for Truncate actions:

--Create the table and Insert the Values

CREATE TABLE TestTable
(empid INT identity(1,1), empname VARCHAR(100))GO

INSERT INTO TestTable
VALUES( 'David' ),
( 'Nicol' ),
( 'James' ),
( 'Larson' )

GO

/***********************************************************************************************************************/

--Truncate table issued
Truncate TABLE TestTable

DECLARE @ObjectName SYSNAME
DECLARE @TransactionID NVARCHAR(500)

--Find the TransactionID

SET @ObjectName = 'dbo.TestTable'
SET @TransactionID = ( SELECT TOP 1 [Transaction ID] FROM fn_dblog(NULL, NULL)
WHERE AllocUnitName = @ObjectNameORDER BY [Transaction ID] DESC)
 
SELECT @TransactionID


-- Retrieving the UserName & Time when the table was truncated based on the TransactionID
 SELECT
[Transaction Name], SUSER_SNAME([Transaction SID]) AS UserName
[Begin Time], Operation, [Transaction ID]
FROM
fn_dblog(NULL, NULL)
WHERE [Transaction ID] = @TransactionID -- @TransactionID
AND [Transaction Name] LIKE 'TRUNCATE%'
AND Operation = 'LOP_BEGIN_XACT'

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

-- Output Results below :

/*******************************************************************************************

Transaction Name Username Begin Time Operation TransactionID

------------------------------------------------------------

TRUNCATE TABLE ckadmin\James 2010/06/16 18:23:03:32 LOP_BEGIN_XACT 00000:000000f0

*******************************************************************************************/
--Drop Table Issued

DROP TABLE TestTable

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

-- Output Results below :

/*******************************************************************************************

TransactionID Begin Time User

------------------------------------------------------------

0000:000000f0 2010/06/16 18:23:03:32 ckadmin\James


*******************************************************************************************/
--Pass the TraansactionID To get the Objectid of the dropped Table
SELECT TOP (1) [Lock Information] FROM fn_dblog (NULL, NULL)
WHERE [Transaction Id] = <TransactionID>
AND [Lock Information] LIKE '%SCH_M OBJECT%';

-- Output Results below :

/*******************************************************************************************

Lock Information

--------------------------------------

HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 25:245575913:0


*******************************************************************************************


25:245575913 is database_id and the object_id


Since the object has been dropped already you may not be able to validate the table name . So log in to the database which has been previously restored from the backup

and run the below statement against the same database to get the table name.*/
SELECT OBJECT_ID(245575913) as TableName

 /**********************************************************************************************************************/