Fix ERROR_LOG_PINNED_ARCHIVE_TAIL (0x000019DF)

This error means your transaction log can't truncate because an active backup or log shipping job hasn't marked the log as backed up. Happens mostly on SQL Server or Exchange.

When This Error Hits

You're running a production SQL Server (2016 or later, but seen it on 2008R2 too) and suddenly the transaction log is full. And I mean full — 100% used. Your monitoring tool shows ERROR_LOG_PINNED_ARCHIVE_TAIL (0x000019DF) in the Windows System log or SQL Server error log. The server is still running, but any write-heavy query either hangs or throws a log-full error.

Common triggers: a backup job failed silently, a log shipping secondary is behind, or someone disabled the log backup schedule without realizing it. Also happens after restoring a database with NORECOVERY and leaving it there for hours.

What's Actually Going On

The transaction log has a virtual log file (VLF) at the tail that SQL Server can't overwrite. Normally, after a log backup, SQL marks those VLFs as reusable. But if the log backup chain is broken — say the last backup failed or the backup device is unreachable — the engine keeps the oldest active VLF pinned. That's the "archive tail." It's like a bookmark saying "I still need this data until someone backs it up."

The error code 0x000019DF is the Windows representation of this condition. SQL Server itself shows it as 9002 (log full) with a message about the log being pinned. The root cause is almost always a backup failure, or an active transaction preventing log truncation.

The Fix — Step by Step

Check the log backup job
Open SQL Server Agent and look for your log backup job. If it's disabled or failing, that's your problem. Run DBCC SQLPERF(LOGSPACE) to see log space used — if it's near 100%, you're stuck.
Run a manual log backup
If the backup job is broken, run a manual log backup to a valid path:
```
BACKUP LOG [YourDatabase] TO DISK = 'D:\Backups\YourDatabase_log.trn'
```
This often clears the pin immediately. If it succeeds, log space drops.
Check for active transactions
If the log backup runs but log space doesn't shrink, there's a long-running transaction. Run:
```
DBCC OPENTRAN('YourDatabase')
```
Find the oldest active transaction and kill it (or wait for it to finish). Then run another log backup.
Force a tail-log backup (last resort)
If the database is in recovery and you can't take a normal log backup, use:
```
BACKUP LOG [YourDatabase] TO DISK = 'D:\Backups\YourDatabase_log.trn' WITH NO_TRUNCATE
```
This captures the tail without truncating, but it gives you a backup so you can do a restore later.
Temporarily switch to simple recovery model (emergency only)
If the log is pinned and you need space now, change to SIMPLE recovery, then back to FULL. This breaks your log chain. Only do this if you can afford a full backup immediately after:
```
ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE;
ALTER DATABASE [YourDatabase] SET RECOVERY FULL;
BACKUP DATABASE [YourDatabase] TO DISK = 'D:\Backups\YourDatabase_full.bak';
```

If It Still Fails

Check disk space on the drive holding the backup files. No room = backup fails = log pinned.
Verify the backup device (tape or network share) is online and permissions are correct.
Look at the secondary in a log shipping setup. If it's disconnected, the log won't truncate. Restore the secondary manually or break log shipping temporarily.
Check for replication. The log reader agent can pin the log if it's stalled. Restart the agent or run sp_replcounters to check.
If all else fails, shrink the log file. This is a band-aid, not a fix:
```
DBCC SHRINKFILE (YourDatabase_log, 1000)
```
This may fail if the tail is still pinned, but it's worth a shot after you've tried everything else.

Pro tip: Set up a monitoring alert on SQL Server error 9002 and on backup job failures. This error never happens out of the blue — it's always preceded by a backup problem. Catch it early and you save yourself the headache.