In some instances, a single long-running transaction can prevent the log from being truncated. These transactions are usually a result of application errors or badly formed queries. It is important to detect the presence of these transactions and act accordingly. Otherwise, the transaction log eventually fills up, even if DUMP TRANSACTION statements have been executed.
The easiest way to detect a long-running transaction is by using DBCC OPENTRAN, which displays information on the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Using the WITH TABLERESULTS option, you can insert the results into a table for easy comparison.
DBCC OPENTRAN is extremely useful in determining whether or not an open transaction exists within the log. When using the DUMP TRANSACTION statement, only the inactive portion of the log can be truncated, so an open transaction could cause the log not to be truncated completely. In earlier versions of SQL Server, all users needed to log off or the server needed to be shut down and restarted to clear the uncommitted transactions from the log. With DBCC OPENTRAN, an open transaction can be identified (the spid, system process ID taken from the sp_who system stored procedure output, is returned) and terminated, if necessary.
After this condition has been detected, your knowledge of any applications using the database can tell you whether or not it represents a problem. For example, suppose that the longest running transaction on a database takes an average of 2 minutes and that the log could not be purged over a 5-minute interval. The average of 2 minutes might have been exceeded because of computer load or SQL Server load, so there is a good chance that no problem exists.
A problem might exist if the log repeatedly fails to be purged over a long period of time, perhaps after several DUMP TRANSACTION statements. This problem can result from any of the following:
If the blocking transaction is due to a runaway query and the transaction has been identified, use the KILL statement to stop the process. This clears the transaction and allows the log to be truncated. For additional information about stopping processes, see Chapter 24, "Additional Problem-Solving Techniques."
If the KILL statement cannot stop the process, you must restart SQL Server to resolve the problem. Restarting SQL Server causes the database to go through normal recovery, so any outstanding transactions are either committed or rolled back.