Dumping a Full Transaction Log

It is possible for a database's transaction log to become so full that there is no room left to log new transactions. When this occurs, SQL Server will report error 1105, stating:

Can't allocate space for object 'syslogs' in database <database name> because the 'logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

In most cases, the log can be dumped to a physical dump device, which will free up new space in the log. When the transaction log is dumped, SQL Server first writes a checkpoint record in the log in order to get a snapshot of which transactions are currently open. It then proceeds to write out the pages of the log and then remove the inactive portion of the log.

If the log is so full that there is not even enough room left to write the checkpoint record, the only option available is to issue a DUMP TRANSACTION database_name WITH NO_LOG statement. This statement truncates the inactive portion of the log without writing the initial checkpoint record and without logging any of the truncation operation. Because the log is not written to a physical dump device, it is not recoverable. It is therefore imperative that the database be dumped immediately so that future transaction log dumps will be recoverable. If you encounter this situation regularly, you should increase the size of the log or increase the frequency of your log dumps.

SQL Server 6.0 includes enhancements that can help prevent a transaction log from ever filling completely. Through its close integration with the Windows NT Performance Monitor, the percentage of log space used for each database can be continuously monitored. Using Performance Monitor alerts, the database administrator can have Performance Monitor automatically run a batch file to dump the transaction log when the percentage of log space used hits a predefined threshold. Using this approach, along with the automated scheduled backup, alerting, and email and page notification capabilities of SQL Enterprise Manager, the database administrator's task of managing backups has been greatly simplified and automated.