Transaction log is full
Posted by Ravi Khanal on Sep 21, 2008
Transaction log is also major cause for slowing the Performance of SQL Server. As a Database Administrator you should monitor your transaction log and check whether it is full or not. Transaction log is a WRITE AHEAD LOG. So whenever there is any data modification in the database, then “before image” and “after image” of any data would be written in the transaction log. If the transactions that are making the data modifications is running or its not being committed then these logs in the transaction log would be the active portion of transaction log. The committed transaction log would be inactive log in the transaction log. The lazy writer thread wakes up after certain period of time and it would write those logs to the physical disks. But sometime if the transaction are too long and its doing lots of updates then it would make the transaction log full and you will get “Out of disk” error. If you are maintaing full Recovery mode and never taking any log backup then its another major cause for transaction log to become full. Transaction log is the important component of SQL Server for recovering from any disaster scenarios. So as a Database Administrator you should always have a close look on the Transaction Log.
So you should use some DBCC command on the regular basis to find out about the Transaction Log.
- DBCC SQLPERF(logspace)
This will tell you how much transaction log is being used.
- DBCC LOGINFO
This will tell you about the virtual logs inside the transaction log.
- DBCC OPENTRAN
This will show you the open transactions or the transaction that are not yet committed.
When you are in the disaster recovery scenario and find out that your transaction log is full then you have to follow these steps:
- Free up the log Space.
- Shrink the log down to a normal size.
- Take a full or differential backup to “resync” your back procedure.
Note: In a emergency case you can use
- Backup log ravisdb with no log
- Backup log ravisdb with Truncate_only
But these command are not recommended since this will break the log chain. So what you can do is take the backup with no truncate first. By doing so the checkpoint is not fired and you would not loose anything from your transaction log. You can check your data and when everything is allright, you can free up your transaction log.
