You are currently offline, waiting for your internet to reconnect

Recover from a full transaction log in a SQL Server database

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

INTRODUCTION
Full transaction logs can make your Microsoft SQL Server database unusable. This article describes how to truncate and shrink the transaction logs when they grow too large. This article also describes how to prevent the transaction logs from growing unexpectedly.

More information
Step one: Reduce the transaction log size


When the transaction logs are full, you must reduce the size of the transaction logs. To do this, you have to truncate the inactive transactions in your transaction log, and then shrink the transaction log file.

Note The transaction logs are very important for maintaining the transactional integrity of the database. Do not delete the transaction log files, even after you make a backup of your database and the transaction logs. Step two: Truncate the inactive transactions in your transaction log


When the transaction logs are full, immediately back up your transaction log file. During the backup of your transaction log files, SQL Server automatically truncates the inactive part of the transaction log file. The inactive part of the transaction log file contains the completed transactions, so the transaction log file is no longer used by SQL Server during the recovery process. SQL Server reuses this truncated, inactive space in the transaction log instead of letting the transaction log continue to grow and use more space.

For more information about the issues that you have to consider when you back up the transaction logs and when you restore the transaction log backups, visit the following topics in SQL Server Books Online:
  • Transaction log backups
  • Transaction log backup and restore
You can also delete the inactive transactions from a transaction log file by using the Truncate method. For more information about truncating the transaction logs, see the "Truncating the transaction log" topic in SQL Server Books Online.

Important After you manually truncate the transaction log files, you must create a full database backup before you create a transaction log backup.

For more information about the issues that may occur when you truncate the transaction log files, click the following article number to view the article in the Microsoft Knowledge Base:
62866 Reasons why SQL transaction log is not being truncated
Step three: Shrink the transaction log file


The backup operation or the Truncate method does not reduce the log file size. To reduce the size of the transaction log file, you must shrink the transaction log file. To shrink a transaction log file to the requested size and to remove the unused pages, you must use the DBCC SHRINKFILE operation. The DBCC SHRINKFILE Transact-SQL statement can only shrink the inactive part of the log file.

Note The DBCC SHRINKFILE Transact-SQL statement cannot truncate the log and shrink the used space of the log file on its own.

For more information about shrinking the transaction log files, see the following topics in SQL Server Books Online:
  • Shrinking the transaction log
  • DBCC SHRINKFILE
For more information about how to shrink the transaction log files in SQL Server 2000, click the following article number to view the article in the Microsoft Knowledge Base:
272318 Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE
For more information about the problems that may occur when you shrink the transaction log files, click the following article numbers to view the articles in the Microsoft Knowledge Base:
814574 PRB: Error message: "Cannot shrink log file ..." occurs when you shrink the transaction log file
324432 PRB: DBCC SHRINKFILE and SHRINKDATABASE commands may not work because of sparsely populated text, ntext, or image columns

Step four: Prevent the transaction log files from growing unexpectedly


To prevent the transaction log files from growing unexpectedly, consider using one of the following methods:
  • Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
  • Configure the automatic expansion of transaction log files by using memory units instead of a percentage after you thoroughly evaluate the optimum memory size.

    For more information about the issues to consider when you configure the autogrow option, click the following article number to view the article in the Microsoft Knowledge Base:
    315512 Considerations for autogrow and autoshrink configuration
  • Change the recovery model. If a disaster or data corruption occurs, you must recover your database so that the data consistency and the transactional integrity of the database are maintained. Based on how critical the data in your database is, you can use one of the following recovery models to determine how your data is backed up and what your exposure to the data loss is:
    • Simple recovery model
    • Full recovery model
    • Bulk-logged recovery model

    You can use the simple recovery model to recover your database to the most recent backup of your database. Or you can use the full recovery model or the bulk-logged recovery model to recover your database to the point when the failure occurred. To do this, restore your database by using the transaction log file backups.

    By default, in SQL Server 2000 and in SQL Server 2005, the recovery model for a SQL Server database is set to the Full recovery model. With the full recovery model, regular backups of the transaction log are used to prevent the transaction log file size from growing out of proportion to the database size. However, if the regular backups of the transaction log are not performed, the transaction log file grows to fill the disk, and you may not be able to perform any data modification operations on the SQL Server database.

    You can change the recovery model from full to simple if you do not want to use the transaction log files during a disaster recovery operation.
  • Back up the transaction log files regularly to delete the inactive transactions in your transaction log.
  • Design the transactions to be small.
  • Make sure that no uncommitted transactions continue to run for an indefinite time.
  • Schedule the Update Statistics option to occur daily.
  • To defragment the indexes to benefit the workload performance in your production environment, use the DBCC INDEXDEFRAG Transact-SQL statement instead of the DBCC DBREINDEX Transact-SQL statement. If you run the DBCC DBREINDEX statement, the transaction log may expand significantly when your SQL Server database is in Full recovery mode. In addition, the DBCC INDEXDEGRAG statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement.

    For more information about defragmenting the indexes in SQL Server 2000, see the following Microsoft website:
More information about transaction log files
In SQL Server 2000 and in SQL Server 2005, each database contains at least one data file and one transaction log file. SQL Server stores the data physically in the data file. The transaction log file stores the details of all the modifications that you perform on your SQL Server database and the details of the transactions that performed each modification. Because the transactional integrity is considered a fundamental and intrinsic characteristic of SQL Server, logging the details of the transactions cannot be turned off in SQL Server.

The transaction log file is logically divided into smaller segments that are referred to as virtual log files. In SQL Server 2000, you can configure the transaction log file to expand as needed. The transaction log expansion can be governed by the user or can be configured to use all the available disk space. Any modifications that SQL Server makes to the size of the transaction log file, such as truncating the transaction log files or growing the transaction log files, are performed in units of virtual log files.

If the transaction log file that corresponds to a SQL Server database is filled and if you have set the option for the transaction log files to grow automatically, the transaction log file grows in units of virtual log files. Sometimes, the transaction log file may become very large and you may run out of disk space. When a transaction log file grows until the log file uses all the available disk space and cannot expand any more, you can no longer perform any data modification operations on your database. In addition, SQL Server may mark your database as suspect because of the lack of space for the transaction log expansion.

For more information about the scenarios that may cause the transaction log file to grow unexpectedly, click the following article number to view the article in the Microsoft Knowledge Base:
317375 Transaction log grows unexpectedly or becomes full on SQL Server

More Resources
For more information about how to reduce the transaction log size, visit the following Microsoft websites:


References
For more information about troubleshooting the additional disk space requirement during the recovery process, see the "Insufficient disk space" topic in SQL Server Books Online. For more information about the transaction log architecture, see the following topics in SQL Server Books Online:
  • Transaction log architecture
  • Transaction log logical architecture
  • Transaction log physical architecture
For more information about the recovery models in SQL Server 2000, see the following topics in SQL Server Books Online:
  • Selecting a recovery model
  • Simple recovery
  • Full recovery
  • Bulk-logged recovery
  • Switching recovery models

TLOG T-log filesize becomes full run out of space fills
Properties

Article ID: 873235 - Last Review: 07/12/2013 08:06:00 - Revision: 9.2

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbsqlsetup kbdiskmemory kbdisasterrec kbhowto kbconfig kbinfo kbcip KB873235
Feedback