How to prevent the transaction log from abnormally growing in SQL Server databases

server| Data | database

The steps that you must perform when the transaction log expands to an unacceptable limit. The extension of the transaction log will cause the Microsoft SQL Server database to become unusable.

In SQL Server 2000, each database contains at least one data file and one transaction log file. SQL Server 2000 stores data physically in the data file. The transaction log file stores the details of all the modifications you have performed on the SQL Server database, as well as the transactions that perform each modification. Because transactional integrity is considered a basic and intrinsic feature of SQL Server, it is not possible to turn off logging of transaction details in SQL Server 2000.

In a logical sense, the transaction log files are divided into smaller segments, which are referred to as virtual log files. In SQL Server 2000, you can configure the transaction log files to be extended as needed. Users can control the expansion of the transaction log, or they can configure it to use all available disk space. Any modifications that SQL Server makes to the size of the transaction log file, such as truncating or increasing the transaction log files, are performed in virtual log files.

If the transaction log files corresponding to the SQL Server database are filled and the transaction log file options are set to grow automatically, the transaction log files will grow in virtual log files. Sometimes, the transaction log files can become so large that there is not enough disk space. If the transaction log files grow and the log files occupy all available disk space and cannot continue to expand, no more data modification operations can be performed on the database. Not only that, because the transaction log lacks extended space, SQL Server may mark your database as a suspicious database.

Reduce the size of the transaction log

If the transaction log has grown to an unacceptable limit and you want to recover from this situation, you must reduce the size of the transaction log. To do this, you must truncate the inactive transactions in the transaction log and shrink the transaction log files.

Note: Transaction logs are important for maintaining transactional integrity of the database. Therefore, even if you make a backup of the database and transaction log, you must not delete the transaction log files.

Truncate transaction log non-active transactions

When the transaction log grows to an unacceptable limit, you must back up the transaction log files immediately. After a backup of the transaction log file is created, SQL Server automatically truncates the inactive portion of the transaction log. The inactive portion of the transaction log file contains the completed transaction, so SQL Server will no longer use the transaction log files during the recovery process. SQL Server will reuse this truncated inactive space in the transaction log without allowing the transaction log to continue to grow and occupy more space.

You can also remove inactive transactions from the transaction log file by truncating the method. For additional information about truncating the transaction log, see the "Truncate 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 the transaction log backup.

Shrink transaction log files

Neither the backup operation nor the truncation method reduces the size of the log file. To reduce the size of the transaction log file, you must shrink the transaction log file. To shrink the transaction log file to the desired size and remove unwanted pages, you must use the DBCC shrinkfile operation. DBCC Shrinkfile Transact-SQL statements can only shrink inactive parts of the log file.

Note: When DBCC shrinkfile Transact-SQL statements are used alone, you cannot truncate the log and shrink the used space in the log file.

Prevent transaction log files from abnormally growing

To prevent the transaction log files from growing abnormally, it is recommended that you use one of the following methods:

• Set the size of the transaction log file to a larger value to avoid automatic expansion of the transaction log file.

• Use memory units instead of percentages to configure automatic expansion of transaction log files after fully evaluating the optimal memory size.

• Change the recovery model. In the event of a disaster or data corruption, you must restore the database to maintain the consistency of your database data and the integrity of your transactions. Depending on how important the data is in the database, you can select one of the following recovery models to determine how you can back up your data and the risks that data loss may pose to you:

• Simple recovery Model (simplicity)

• Complete recovery Model (full)

• Bulk-logged recovery model (bulk-logged)

With the simple recovery model, you can restore the database to the most recent database backup. With the full recovery model or the bulk-logged recovery model, you can restore the database by using a transaction log file backup to restore the database to the point of failure when the failure occurred.

By default, in SQL Server 2000, the recovery model for the SQL Server database is set to the full recovery model. In the full recovery model, the transaction log is backed up regularly to prevent the transaction log files from growing too large to be in serious imbalance with the size of the database. By contrast, if you do not perform a regular backup of the transaction log, the transaction log files grow to fill the entire disk, and you may not be able to perform any data modification operations on the SQL Server database.

If you do not want to use the transaction log files during a disaster recovery operation, you can change from the full recovery model to the simple recovery model.

• Regularly back up transaction log files to delete inactive transactions in the transaction log.

• Design a transaction as a small transaction.

• Ensure that no attempted transactions continue to run indefinitely.

• Schedule the UPDATE STATISTICS option to run daily.

• To defragment an index to improve workload performance in a production environment, use DBCC INDEXDEFRAG Transact-SQL statements instead of DBCC DBREINDEX Transact-SQL statements. If you run the DBCC dbreindex statement, the transaction log may be greatly expanded when the SQL Server database is in full recovery mode. In addition, the DBCC INDEXDEGRAG statements do not hold locks as long as the DBCC DBREINDEX statements.

If you must run the DBCC dbreindex statement, because this is a job and is part of a database maintenance plan, you must decompose the job into multiple jobs. In addition, the transaction log must be backed up frequently in the interval that these jobs are performed.