It is possible for a transaction log to become so full that there is no room left to log new transactions. When this occurs, SQL Server reports error 1105:
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 to get a snapshot of which transactions are currently open. It then writes out the pages of the log and removes 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 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 e-mail and page notification capabilities of SQL Enterprise Manager, the database administrator's task of managing backups is greatly simplified and automated.