Day 21 of 31 Days of Disaster Recovery: Who Deleted That Data?

Welcome back for day 21 of my 31 Days of Disaster Recovery series. Today I want to talk about trying to track down who deleted data from a table. This little investigation started out as a question on the #sqlhelp hash tag on Twitter from Wayne Sheffield (blog|@DBAWayne) whom I first met on SQLCruise Alaska 2012.

The question was asking how to convert the page ID from fn_dump_db_log() to match the integer format for page ID in DBCC IND(). Where we ended up was not even close to where we started.

If you want more info on the undocumented function fn_dump_dblog(), check out the following blog post: Day 19 of 31 Days of Disaster Recovery: How Much Log Can a Backup Log.

If you missed any of the earlier posts in my DR series, you can check them out here:

31 Days of disaster Recovery
1. Does DBCC Automatically Use Existing Snapshot?
2. Protection From Restoring a Backup of a Contained Database
3. Determining Files to Restore Database
4. Back That Thang Up
5. Dealing With Corruption in a Nonclustered Index
6. Dealing With Corruption in Allocation Pages
7. Writing SLAs for Disaster Recover
8. Resolutions for All DBAs
9. Use All the Checksums
10. Monitoring for Corruption Errors
11. Converting LSN Formats
12. Extreme Disaster Recovery Training
13. Standard Backup Scripts
14. Fixing a Corrupt Tempdb
15. Running DBCC CheckTable in Parallel Jobs
16. Disaster Recovery Gems From Around The Net
17. When are Checksums Written to a Page
18. How to CHECKDB Like a Boss
19. How Much Log Can a Backup Log
20. The Case of the Backups That Wouldn’t Restore

Converting Page ID

I explained that the Page ID column in fn_dump_dblog() output has file ID and page ID in hexadecimal format as : . It was as simple as splitting the value and then converting both pieces to integers. I recommended using the same expression to convert Page ID as I did for converting the LSN in the blog post Day 11 of 31 Days of Disaster: Converting LSN Formats. Wayne’s plan was to search the log backups from the time frame that they believed the deletion had occurred for entries where the page ID matched one of the page IDs output by DBCC IND() for the table in question in hopes of finding a clue as to what performed the delete, a person or the system.

Wayne quickly discovered an idea better than using the page ID to find entries that matched the entire list of page IDs from DBCC IND(). He could use the AllocUnitId column to correlate it to the object ID of the table. The next trick was to get the AllocUnitId to match up to the table’s object ID. We did this by joining fn_dump_dblog() to sys.allocation_units and then joining that to sys.partitions.

The process for finding these records was Wayne’s brain child. I just helped work out some details of the query. I’ll leave the demoing of his process up to him if he chooses to share it. I will share a query for finding the log entries from fn_dump_dblog() that correlate to a specific object.

-- Define object from which data was deleted
Declare @ObjectID int;
Set @ObjectID = OBJECT_ID('dbo.AllDatabases');

-- Query for log file entries
Select DD.*
From fn_dump_dblog(null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null) DD
Inner Join sys.allocation_units AU
    On AU.allocation_unit_id = DD.AllocUnitId
Inner Join sys.partitions P
    On P.partition_id = AU.container_id
Where DD.AllocUnitId Is Not Null
And P.object_id = @ObjectID;
Go

Summary

In the end, Wayne was successful in tracking down the entries in the log backup file for the data deletion from the table. Sadly, the log records do not tell you who performed the action. He was able to get the session IDs (SPIDs) for the commands which indicated it was a user thread that ran the delete and not a system thread. Although you can’t find everything you want in the log, you can still find a lot of information and correlate that to certain conclusions. And I especially like the fact that you can use fn_dump_dblog() on a log backup rather than a live database because you can muck around in the log data without worry of affecting a live database.