Search This Blog

Thursday, September 17, 2026

SQL Server Transaction Log Forensics: Preserving Evidence During an Incident

SQL Server Transaction Log Forensics: Preserving Evidence During an Incident SQL Server Transaction Log Forensics: Preserving Evidence During an Incident
This is the companion to Why sys.fn_dblog Is Still Undocumented And Still There. That one was about why the transaction log stays undocumented. This one is about how:  What to do in the first fifteen minutes after someone says "the data's gone," when the log still holds the answer and almost everything your instance does routinely is quietly destroying it.

A note on scope: this is a preservation and investigation guide, not a full disaster-recovery playbook. If you are facing a real production data-loss incident, contact Microsoft Support right away, alongside your internal incident-response team. Do not wait until you have tried everything here before escalating. The goal is to preserve your options while help is being brought in, not to replace support-led recovery guidance.

The log is designed to forget

Here's the the problem. The transaction log is the only place that remembers what actually happened, and the log is designed to forget. Truncation, checkpoints, log backups, shrink jobs, and instance restarts are all normal, healthy behaviors and every one of them can take your evidence with it.

Worse, several of the instincts that feel most responsible during an incident are the ones that do the damage:

  • "Let me take a log backup to be safe."  alears the active log.
  • "Let me restart the instance and see if it clears up." triggers recovery and a checkpoint.
  • "The log's grown huge, let me shrink it." actively reclaims the space holding your evidence.
  • "Let me detach and copy the files somewhere safe." worst case, you can't reattach cleanly.

So the first rule is unglamorous: stop touching things. The second rule is that preservation comes before investigation, and investigation comes before recovery. Get those in the wrong order and you spend the rest of the night wishing you hadn't.

First: Stop The Bleeding

Before you query anything, do these.

Announce a change freeze on the affected database. Application deploys, maintenance jobs, ad-hoc cleanup scripts all paused. Every write pushes your evidence closer to the edge of the active log.

Disable the jobs that destroy evidence, in this order:

  1. Any log shrink job. Shrink reclaims exactly the space your evidence is sitting in. If the volume is genuinely full you may have no choice but to shrink, but exhaust the alternatives first: free space elsewhere on the drive, or add a second log file on another volume to buy room. Shrink last, knowing you're trading evidence for space.
  1. Any index rebuild or maintenance job. These generate enormous log volume and will push your records out.
  1. Hold off on the log backup schedule, but read the disk-space warning below before you disable it.

Do not, under any circumstances yet: restart the instance, detach the database, run CHECKPOINT manually, take a non-copy-only log backup, fail over the AG, or start a restore over the top of the live database.

Write down the time. Every step from here should be timestamped in a notes file. You will need it, either for the postmortem or because someone will eventually ask you to prove what you did.

Step 1: Find out whether you have a game to play

Your recovery model determines everything that follows.

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE database_id = DB_ID('YourDatabase');

FULL: You're in the best position. Log records survive until a log backup truncates them, and your backup chain lets you restore to a specific LSN.

BULK_LOGGED: Mostly like FULL, with a caveat covered later.

SIMPLE: The news is bad. A checkpoint clears the log, and checkpoints happen constantly. There is no point-in-time recovery. Your realistic options are the last full/differential backup and whatever non-log evidence you can find. Don't spend twenty minutes on fn_dblog hoping; check it just once, and if it's empty, move on.

That log_reuse_wait_desc column is worth a second look, because for once it may be working in your favor. If it shows LOG_BACKUP, ACTIVE_TRANSACTION, AVAILABILITY_REPLICA, or a replication reason, something is preventing truncation right now, meaning your evidence is being held in place by the very thing that normally annoys you (Troubleshoot a full transaction log). The same value is exposed as log_truncation_holdup_reason in sys.dm_db_log_stats.

On an Availability Group, AVAILABILITY_REPLICA means the primary is waiting to ship log to a secondary that's lagging or down. Annoying on any other day, it's a gift during an incident.

Step 2: Get the evidence out of the log

This is the single most important step, and the one most people skip. Don't investigate in the log. Copy the log's contents into a table you control, then investigate the copy.

USE YourDatabase;
GO
SELECT * INTO Forensics.dbo.LogDump_20260916
FROM sys.fn_dblog(NULL, NULL);

Persisting the output into another database is a well-worn technique (Filter Results of fn_dblog function). Two reasons it matters enormously here:

  • Truncation can no longer hurt you. The records are now rows in a table with their own backup and recovery path.
  • The investigation gets dramatically faster. Reading the log is slow, and you will query this data dozens of times as you narrow the search. The same advice applies when reading backups with fn_dump_dblog.

Put the target table in a different database, ideally on a different instance. Writing your evidence into the database you're investigating adds log volume to the very log you're trying to preserve.

On a busy database the active log can hold millions of records, so if the unfiltered SELECT INTO is too heavy, bound it by LSN range or filter on Operation, but try to capture it all first if you possibly can. You can always narrow a table you already have.

Step 3: Capture a file, without truncating anything

A table copy is perfect for the active log. But if the records you want have already rolled out, you need backup files and getting one without destroying the active log is where most people go wrong.

A routine log backup truncates. Microsoft's BACKUP reference says it outright: "After a typical log backup, some transaction log records become inactive, unless you specify WITH NO_TRUNCATE or COPY_ONLY".

On a healthy, online database use a copy-only log backup:

BACKUP LOG YourDatabase
TO DISK = 'E:\Forensics\YourDatabase_incident.trn'
WITH COPY_ONLY, INIT, CHECKSUM;

A copy-only log backup preserves the existing log archive point and doesn't affect the sequencing of your regular log backups (Copy-only backups). You get a readable .trn file, your active log stays intact, and your backup chain is undisturbed.

For a damaged database you intend to restore, take a tail-log backup:

BACKUP LOG YourDatabase
TO DISK = 'E:\Forensics\YourDatabase_tail.trn'
WITH NORECOVERY, NO_TRUNCATE;

If the database is online and you plan to restore it, back up the tail of the log first, using WITH NORECOVERY to avoid an error on an online database (Tail-log backups), and the point-of-failure restore procedure uses exactly this NORECOVERY, NO_TRUNCATE form. Note what NORECOVERY does: it leaves the database in RESTORING state. That's correct when you're committing to a restore, and completely wrong when you're still investigating a database that users are hitting. Know which situation you're in.

If the log is damaged badly enough that NO_TRUNCATE fails, you can attempt the tail-log backup with CONTINUE_AFTER_ERROR instead. And if the log files are damaged and no tail-log backup is possible at all, you restore without one and accept losing everything committed since the last log backup.

Also grab a copy-only full backup if you don't have a recent one. Full backups don't truncate the log, and having a known-good starting point makes every later decision reversible.

Collect the existing log backup chain, too. Copy the .trn files from the incident window somewhere your retention cleanup can't reach. Your own retention job is a real threat here as it deletes old backups on schedule and doesn't know today is special.

Step 4: The log backup schedule question

Now the trade-off I got wrong the first time I thought this through, and which deserves stating plainly.

Pausing the log backup schedule protects the active log. It also means:

  • The log grows unchecked. On a busy system it can fill the drive and take the database offline. You'd convert a data-loss incident into an outage,  a strictly worse incident.
  • Your recovery position stops advancing. From the moment you stop taking log backups, you can no longer restore to any point after the last one. If the investigation goes sideways and you do need a point-in-time restore, you've narrowed your own options.

So the answer isn't "pause the schedule." It's "make the schedule irrelevant first." Steps 2 and 3 do exactly that: once the records are in a table and in a copy-only backup file, a routine log backup truncating the live log costs you nothing.

If you genuinely need to pause it and you haven't captured anything yet, and you need minutes to get organized then pause it, watch free disk space actively, and set yourself a hard time limit. Don't let a paused schedule survive the incident.

Shrink jobs sit differently. A log backup at least buys you something, it advances your recovery position while it truncates. A scheduled shrink buys you nothing but disk space you weren't short of, which is why it should be off during an incident and, honestly, off the rest of the time too. The exception is the one case that isn't a schedule at all: the volume is actually filling and you're out of alternatives. That's a deliberate decision to trade evidence for staying online.

Step 5: Corroborate from outside the log

The log tells you what changed. It's poor at telling you what statement ran or who was connected. Several sources in SQL Server can help with that, and they're all fragile, by that I mean mean the default trace will roll over, ring buffers will wrap etc...

The default trace captures object created, altered, and dropped events, and it's running right now unless someone disabled it. This is the fastest path to "who dropped that table", Pinal Dave's walkthrough and the DallasDBAs version both read the trace files directly (SQL SERVER – Who Dropped Table or Database, see also DallasDBAs). Copy the .trc files out immediately; the default trace uses rollover files and will overwrite itself.

The system_health Extended Events session starts automatically with the Database Engine and runs with no noticeable overhead, collecting diagnostic data continuously. It's the diagnostic log almost nobody reads. Copy its files too.

Also worth grabbing while you're at it: the SQL Server error log files, output from any SQL Server Audit or third-party auditing you have running, application logs for the same window, and if you're lucky enough to have it , Query Store, which survives restarts and may show you the offending statement text.

Copy all of it to your forensics folder before you do anything else invasive. Files are cheap; a rolled-over trace is gone.

Step 6: Work out what actually happened

Now query your copied table. What you're looking for depends on the operation, and the operations don't look alike.

A DELETE logs one record per row. Delete ten rows and you get ten LOP_DELETE_ROWS records. Good news: row-level detail.

A TRUNCATE TABLE does not. It removes rows without logging the individual row deletions, it deallocates pages wholesale rather than deleting records one by one. It is still fully logged and still rolls back; there's no such thing as a non-logged operation in a user database. Veteran DBAs have been asking for a no-logging option for user databases for as long as there have been DBAs, and the answer has always been no because logging isn't a bookkeeping tax, it's the mechanism that makes rollback and recovery possible at all.. 

But you will not find your rows enumerated in the log. You'll find deallocation, which tells you that it happened and when, not what was in it. For a truncate, restore-based recovery is essentially your only path.

An UPDATE may log only the changed fragment rather than the full before-and-after row image. This is the single most common source of disappointment: "I can see the update, why can't I reconstruct the old value?"

To find who, pull the [Transaction SID] from the LOP_BEGIN_XACT record for the transaction and pass it to SUSER_SNAME() (See Paul Randal's SQLskills). Then join back on [Transaction ID] to see everything that transaction did. The LOP_BEGIN_XACT record is also where you get the transaction's start time and name which is what turns a pile of records into a timeline. The general pattern of filtering by Operation and AllocUnitName to isolate the damage is well documented.

What you want out of this step is three specific things: the LSN where the bad transaction began, the user, and the blast radius which objects, how many rows.

Step 7: Reading older evidence from backups

If the records aren't in the active log, they're in your log backups, and fn_dump_dblog reads those. It's the same idea as fn_dblog pointed at a file, with a long parameter list that's mostly NULL.

Two things to plan for. First, it's slow, and it can chew through entire backup files and dump its output into a table the same way, then query the table. Second, you may need to walk backwards through several backups to find the right window, which is exactly the tedium Paul Randal's walkthrough exists to spare you.

One consolation on timing: truncation only marks VLFs as reusable rather than erasing them (Transaction log Truncate vs Shrink vs VLF number), and a VLF can only be marked reusable when nothing still needs its records. So records sometimes survive longer in VLFs than the truncation point suggests, and trace flag 2536 can expose the inactive portion. Treat that as luck, never as a plan.

Step 8: Choose the shape of the recovery

You have your LSN. Now the decision that people get wrong under pressure: restore beside the database, not over it.

SQL Server has no native single-object restore, there is no RESTORE TABLE, and it's been a standing feature request for years. Third-party log readers do offer table-level recovery through a GUI, and it can be a genuine time-saver but note what they're actually doing. They read the log and generate compensating DML to undo the change; they are not restoring an object out of a backup, because the engine gives them no way to. That means they inherit every limitation in this article: they need the relevant log records to still exist, and they're subject to the same undocumented-format caveats. Without one of those tools, the standard move is a side-by-side restore… bring the backup up under a different database name, extract what you need, and copy the rows back into the live database while it keeps serving. The restore sequence is identical either way: most recent full, then the most recent differential based on it, then every log backup after that in order.

Restoring in place turns a data-loss incident into an outage, and if you get the stop point wrong you have to start over from the full backup. Side-by-side costs disk and buys you unlimited attempts.

For the stop point, if you have a clean timestamp, STOPAT is simplest:

RESTORE LOG YourDatabase_Copy
FROM DISK = 'E:\Backups\YourDatabase_log.trn'
WITH STOPAT = '2026-09-16 01:18:00', NORECOVERY;

If you need LSN precision and after Step 6 you have it, use the mark options. STOPATMARK = 'lsn:<lsn_number>' makes the record containing that LSN the recovery point and rolls forward through it; STOPBEFOREMARK stops immediately before it. For undoing a bad transaction you almost always want STOPBEFOREMARK against the LSN of its LOP_BEGIN_XACT. Watch the format conversion, the LSN in log output uses colon-delimited hex and needs converting for the restore syntax (Coeo).

Then reconcile. Everything that happened after your stop point is also absent from the restored copy, so copying rows back means thinking about which changes were legitimate. This is where a narrow blast radius from Step 6 pays for itself.

What the log will not give you

Set expectations early, with yourself and with whoever is asking for hourly updates.

  • Fragment-only updates. As above, an UPDATE may log only the changed portion, so clean before-image reconstruction is the exception rather than the rule.
  • TRUNCATE and DROP give you deallocation, not row contents.
  • Minimally logged operations. Under BULK_LOGGED, bulk imports, SELECT INTO, and similar are minimally logged, and log backups covering them can't support point-in-time recovery within that window. If a minimally logged operation ran since the last log backup and a data file is damaged and offline, a tail-of-the-log backup isn't possible at all.
  • TDE. Encrypted log content limits what any reader can hand back.
  • Schema drift. Decoding an old log record requires knowing the table's schema as it was then. If columns changed since, reconstruction gets shaky fast.
  • In-Memory OLTP. Memory-optimized tables merge multiple row changes into single log records and don't log index modifications at all. Row-level reconstruction assumptions don't hold.
  • PaaS. Azure SQL Database doesn't expose the transaction log at all. There, your answer is point-in-time restore and whatever auditing you enabled in advance.

And the standing caveat: everything here rests on undocumented functions that Microsoft can change or remove in any version, with no support recourse. Record the exact build you validated your scripts against, and re-verify after every upgrade.

If it's an Availability Group

A few extra moving parts.

Don't fail over during the investigation unless you have to. You'll change which replica's log you're reading and complicate your own timeline.

Run fn_dblog on the primary. Read-only secondaries will fight you, and the primary is the authoritative copy.

Check log_reuse_wait_desc first. AVAILABILITY_REPLICA means truncation is already blocked, which is working in your favor. Don't "fix" it mid-incident.

Remember log backups may be running on a secondary. If your backup job lives elsewhere in the AG, disabling the job on the primary accomplishes nothing. Find where it actually runs.

Replication and CDC change the picture too. Both hold log records until their readers catch up, so they may be preserving evidence for you and their own metadata tables are an independent record of what changed.

Quick version

If you only remember one thing, remember the order:

Freeze. Pause shrink and maintenance jobs unless you absolutely have to. Don't restart, detach, CHECKPOINT, fail over, or take a normal log backup.


Check the recovery model. SIMPLE means go straight to backups.

Copy the log out. SELECT * INTO OtherDB.dbo.LogDump FROM sys.fn_dblog(NULL, NULL);

Capture files without truncating. BACKUP LOG … WITH COPY_ONLY plus a copy-only full, plus the existing .trn chain, moved beyond reach of retention cleanup.

Grab the outside evidence. Default trace .trc files, system_health files, error logs, audit output. They roll over.

Then investigate the copies, unhurried: find the LOP_BEGIN_XACT LSN, the SID, the blast radius.

Restore side-by-side with STOPBEFOREMARK, never over the top.

Preservation, then investigation, then recovery. The whole discipline is refusing to do them out of order.

Agatha Christie's Hercule Poirot solved his cases with two things: order and method, and the little grey cells. He never once ran to the scene and started moving the furniture. Neither should you, the log is your crime scene, and every tempting quick fix is a footprint in the flowerbed.

Prepare for the Next Incident: Auditing, Recovery, and Runbooks

The reason this article has to exist is that the log is a lousy audit trail being pressed into service as one. Fix that. Once the immediate incident is resolved, address the gaps that made the investigation difficult. The goal is to have purpose-built records, tested recovery procedures, and a clear runbook ready next time, rather than having to reconstruct events from transaction-log internals.

  • SQL Server Audit for who-did-what, with a real retention story.
  • Temporal tables on anything where "what did this row look like before" is a question you'll ask more than once. This is the single highest-value change for accidental-update incidents.
  • Change Data Capture or change tracking where you need the changes themselves and these are documented, supported interfaces, which is the whole point.
  • Extended Events sessions for DDL, since the default trace is small and rolls over fast.
  • Verify your restore chain regularly, including a real side-by-side restore drill. Almost everything in Step 8 goes better if you've done it once when nothing was on fire.
  • Sort out the sysadmin question in advance. If reading the log requires sysadmin and your on-call DBA doesn't have it, you'll spend your best twenty minutes on an access request.
  • Write the runbook. Fill in your own paths, job names, instance names, and where the forensics folder lives. A checklist you wrote calmly is worth more than anything you'll reason out under pressure.

One last thing worth saying out loud: log forensics is the tool of last resort, and its best use is buying you information, not restoring your data. The restore is what restores your data. The log is what tells you where to stop.

Know when to bring in Microsoft Support

The log reading commands discussed here are undocumented and unsupported. Their usefulness does not make them a supported recovery procedure. Despite everything this article covers and links to, contact Microsoft Support early, especially if your backups cannot cover the potential data loss or cannot be restored. That advice applies even if you have decades of experience and have handled similar incidents before.

Use this information to understand the internals, preserve evidence, and organize your findings while Microsoft Support investigates. The goal is to save time and keep recovery options open, not to replace expert assistance or guarantee that lost data can be recovered.
Try it for yourself with this self-contained T-SQL demo. It creates a sample database, simulates data changes, and walks you through investigating the evidence using sys.fn_dblog. It includes setup instructions and optional cleanup. Run it only on a disposable, non-production SQL Server instance.

Further reading

Backup and restore mechanics

Reading the log

Corroborating evidence

Limits and gotchas