SQL Server Transaction Log Forensics: Preserve Evidence Before You Act

# What this is and what to do first If someone says "the data's gone," stop doing things that look helpful. The SQL Server transaction log is the single source of truth for what happened, and routine operations intentionally discard log records. Your first goal is preservation, then investigation, then recovery — in that order.

# Immediate action checklist

• Announce a change freeze for the affected database: stop deployments, application updates, and ad-hoc scripts.
• Disable jobs that destroy log evidence, in this order: log shrink jobs, index rebuilds and maintenance jobs, and then consider pausing the log backup schedule only after understanding disk-space needs.
• Do not, under any circumstances yet: restart the SQL instance, detach the database, run CHECKPOINT manually, take a non-copy-only log backup, fail over an Availability Group, or start a restore over the live database.
• Record timestamps for every action in a notes file for the postmortem and any support engagement.

# Know whether you have a chance: check the recovery model

• FULL: Best case. Log records remain until a log backup truncates them, so you can often do point-in-time analysis and restores.
• BULK_LOGGED: Similar to FULL for most purposes, but be aware of bulk operation caveats for point-in-time restores.

Also inspect log_reuse_wait_desc (or log_truncation_holdup_reason in sys.dm_db_log_stats). Values such as LOG_BACKUP, ACTIVE_TRANSACTION, or AVAILABILITY_REPLICA can mean truncation is currently blocked and your evidence is being held in place.

# The one essential preservation step: dump the log to a table you control Do not investigate directly against the live log. Copy the log output into a persisted table in another database so truncation and checkpoints cannot delete your evidence and so queries run faster.

• Once in a table you own, the rows follow your normal backup and recovery path and cannot be removed by normal log truncation.

The same approach applies when reading backups with fn_dump_dblog — persist the results before analysis.

# Practical cautions when disk space is tight If the log has grown and you feel pressure to shrink it, exhaust alternatives first: free space on the drive or add a second log file on another volume to buy time. Shrinking reclaims exactly the space that may hold your evidence and should be the last resort.

# When to call for help This guide is a preservation and investigation checklist, not a substitute for Microsoft Support or your internal incident-response team. If this is a real production data-loss incident, contact Microsoft Support immediately and keep them informed of the preservation steps you've taken.

# Quick sequence to follow

1. 1Announce change freeze and disable destructive jobs. 2. Timestamp actions. 3. Check recovery model and log truncation reason. 4. Persist fn_dblog output into a controlled database/table. 5. Only after evidence is secured, proceed with deeper investigation and recovery steps with support involved.