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. Read the rest of this entry »
