Sql-server – Microsoft SQL Server – What does it mean that a Transaction Log is Full


What does it mean that a Transaction Log is Full? I have it the file set to grow 20% when needed. I have 4GBs left on the drive. How do I solve this issue permanently?
Running these commands solves the issue temporarily:

DBCC SHRINKFILE('MyDatabase_log', 1)
DBCC SHRINKFILE('MyDatabase_log', 1)

Best Solution

The Transaction Log is where SQL server 'Records' every change it makes so that if something goes wrong, (From software crash to Power failure, to an asteroid strike... well maybe not an an asteroid strike), it can "recover" by "undoing" all the changes it has made, since the last consistent "CheckPoint" - back to what was that last "Consistent" state of the database... at that checkpoint. Every time a Transaction completes (or "commits"), all the changes that have been stored in the Transaction Log are flagged as "ok", and the CheckPopint marker is allowed to be moved forward to after those changes, so that a future recovery will only "undo" changes to some point after that. After this happens, all the entries in the Transaction Log from before the CheckPoint are no longer needed to recover from a system crash... but they still may be needed to recover from a Hard Disk crash, so...

As the other gentleman mentioned, the "recovery model" you have set up on the server controls what happens to Transaction Log entries from before the checkpoints. In Simple Mode, they are deleted when a checkpoint occurs, but you are at risk if the main data disk crashes, because your transaction log will not contain the changes written to disk since the last backup.

In the other recovery models, the Transaction Log entries are not deleted until you do a Backup, thus protecting you against this risk...

So, generally, when this issue occurs, it's because the server is in one of the "normal" (not simple) recovery models set up, (Incremental or Full) and they are not doing backups... . In this case the Transaction Log just keeps Growing..., and Growing... kinda like those prostate ads on TV...