Yeah, this one's a pain. You're staring at a SQL Server that's suddenly throwing ERROR_LOG_PINNED_RESERVATION and the transaction log is stuck at 99% full. Don't panic. This isn't a hardware failure or a corrupt log — it's a reservation issue, and the fix is straightforward once you know what you're looking at.
The Quick Fix (Do This First)
Open SQL Server Management Studio and run this:
DBCC OPENTRAN('YourDatabaseName') WITH TABLERESULTS;
That shows you the oldest active transaction. Write down the SPID and the BeginTime. Now you have two paths:
- If the SPID belongs to a session you recognize (like a stuck app process), kill it with
KILL <SPID>. - If it's an orphaned transaction from a disconnected session, you still need to kill it — same command works.
Once that transaction is gone, the reservation releases. Then run:
DBCC SHRINKFILE('YourLogicalLogFileName', 0);
That brings the log file back to a reasonable size. Done. Most of the time, that's all there is to it.
Why This Works
Here's the deal. SQL Server reserves log space for transactions that are still open. When a transaction runs long — or never completes — the reservation pins the log. The error message says "reservation consuming most of the log space," but what it really means is an active transaction holds a chunk of the log in a virtual log file (VLF) that can't be truncated. The log looks full, but it's not really full — it's just stuck.
The culprit here is almost always a transaction that started, then the session dropped or the app hung. SQL Server keeps that transaction alive because it doesn't know the client gave up. DBCC OPENTRAN exposes it. Killing it forces a rollback, which frees the reserved VLFs. Then shrink can do its thing.
Quick Note on VLF Anatomy
If you're curious, the log file is split into virtual log files. A pinned reservation holds at least one VLF, often more. The key point: never let a log get above 70-80% full in normal operation. Once it hits 100%, you're in trouble because there's no room for checkpoints to mark VLFs as inactive.
Variations and Edge Cases
Sometimes the simple fix doesn't get it. Here are the less common scenarios I've run into over the years.
Replication or Log Shipping Stuck
If you're using transactional replication or log shipping, the distribution agent or secondary restore job might be holding the log. Run sp_repltrans to see undis-tributed transactions. If you find some, you need to fix the subscriber or, in a pinch, mark the distribution as done:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1;
That clears the backlog. But be careful — only do this if you're okay losing unsynced changes. I always check with the team first.
CDC (Change Data Capture) Leftovers
Change Data Capture can also pin the log if the capture job stops. You'll see high log space used and no active user transactions. The fix is to restart the capture job, or if CDC is no longer needed, disable it properly:
EXEC sys.sp_cdc_disable_db;
That releases the reservation and lets you shrink the log.
Always On Availability Groups
If you're in an AG, the secondary replica might be lagging. The primary log can't truncate until the secondary acknowledges. Check the AG dashboard for suspend status. If a secondary is stuck, resume it via PowerShell or the GUI. If it's hopelessly broken, removing it from the AG frees the log immediately — but that's a bigger decision.
Preventing This from Happening Again
Prevention is about hygiene. First, stop using SIMPLE recovery if you're running into this regularly — that's a band-aid, not a fix. Switch to FULL recovery and take regular log backups. Log backups truncate the log, so the VLFs cycle properly.
Second, set up an alert for log space usage. Anything above 70% should page you. Use the built-in SQL Agent alert or a custom script — doesn't matter, just get it in place.
Third, watch out for long-running transactions. Put a timeout on your app's transactions. A transaction that runs for hours is a landmine. Set LOCK_TIMEOUT or use your ORM's built-in timeout. I've seen apps hold transactions open overnight because a developer forgot to commit — that's how this error shows up at 3 AM.
Finally, check your VLFs. If you've been doing random shrinks for years, your log file might be a mess of tiny VLFs. Rebuild the log with a one-time shrink to a small size, then grow it in chunks to a final size that handles your workload. A clean log file handles pressure better.
That's it. Fix it now, fix it properly, and move on. You've got better things to do than babysit a transaction log.