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
- Does DBCC Automatically Use Existing Snapshot?
- Protection From Restoring a Backup of a Contained Database
- Determining Files to Restore Database
- Back That Thang Up
- Dealing With Corruption in a Nonclustered Index
- Dealing With Corruption in Allocation Pages
- Writing SLAs for Disaster Recover
- Resolutions for All DBAs
- Use All the Checksums
- Monitoring for Corruption Errors
- Converting LSN Formats
- Extreme Disaster Recovery Training
- Standard Backup Scripts
- Fixing a Corrupt Tempdb
- Running DBCC CheckTable in Parallel Jobs
- Disaster Recovery Gems From Around The Net
- When are Checksums Written to a Page
- How to CHECKDB Like a Boss
- How Much Log Can a Backup Log
- 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
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.
Day 29 of 31 Days of Disaster Recovery: Using Database Snapshots to Restore Replicated Databases in Test | SQLSoldier
[…] Who Deleted That Data? […]