SQL Transaction Log is Too Big or Growing Unexpectedly

jeffluo

Problem

Often we see, the transaction log space gets full in SQL Server, due to some appropriate actions not taken by the user when the transaction log keeps growing in SQL server. It creates a problem for user in using SQL Server, as transaction log is too big.

Why the Transaction Log Keeps Growing in SQL Server ?

There could be several reasons behind the transaction log is too big or running out of space. A Plain transactions remains uncommitted when the user do not use explicit COMMIT or ROLLBACK command. It occurs more frequently when application issues CANCEL or Transact- SQL KILL command without using ROLLBACK command. Then, the transaction cancellation occurs, but it will not roll back. Due to which SQL cannot truncate every transition, which occurs after it because aborted transaction remains open. It causes as a big transaction log issue and typically you receive SQL Server error 9002 when the transaction log file cannot expand any longer

How to Prevent Transaction Log Files from Growing Unexpectedly ?

Some solutions that are mentioned below makes easy for users to resolve the problem of Transaction log is too big on their SQL Server.

Backups of Transaction log

If user’s database recovery model is set to full logged, then it is totally VITAL, with which users can make transaction log backups along with full backups. In SQL Server 2005, the databases are set to full recovery model by default. Therefore, user must create backups of log before getting into problem. They can run the mentioned query on their SQL Server instance for the recovery model of databases

SELECT name, recovery_model_desc
FROM sys.databases

User must take the full database backup as full backups are the starting point for any type of recovery procedure and it is important when user runs into trouble of data loss. In fact, backups of transaction logs cannot be created without having the full backup of data.

Bulk or Full-Logged of Recovery Mode

Through Bulk or Full-logged recover mode, all the inactive transactions remain in the transaction log file after Checkpoint is processed and backup of transaction log is made. Its backup performs a truncation for inactive portion of transaction log that allows it to reuse it for the future transactions. This truncation does not shrink a file; it does not allow reusing the space in the file. Due to this, the transaction log file keeps on growing.

Note: Full backup does not eliminate inactive transactions from the log of transactions.

Not creating the backup of transaction log is the main cause due to which transaction log is growing too large. However, some other situations prevent from being removing the inactive transactions by creating regular log backups. The mentioned query is used to have an idea to prevent transaction log from being truncated.

SELECT name, log_reuse_wait_desc
FROM sys.databases

Long-Running Active Transaction

It helps to prevent the truncation of transaction log. All these types of transactions can range the transactions that are being blocked from completing open transactions that are waiting for the user’s input. In case, the transaction remains open, then larger transaction can grow. User can run the following statement, i.e. DBCC OPENTRAN to check the longest running transactions on SQL Server instance. If there is an open transaction then, it will offer session_id (SPID) of the connection, which has transaction open. User can pass this session_id to sp_who2 to control that which connection is open.

Transactional Replication

In, it the inactive portion of transactional log is not truncated until it has been replicated to distributor. It may be due to distributor, who is overloaded and have problem in accepting transactions. It may be because of the log reader agent who should run it often. If DBCC OPENTRAN shows that, the oldest active transaction is replicated. It was open for significant amount of time and it may be user’s issue.

Mirroring of Database

It is somewhat similar to the replication of transaction, which requires that the transaction remain log until record has been written to disk on mirror server. If the server of mirror instance falls behind the server principal of instance then, the amount of active log space will grow. In such a case user needs to stop the database mirroring, take a long backup of truncates log by applying that log backup to database of mirror and start the mirroring again.

Disk Space

Mirroring of database is somewhat similar to transactional replication, which requires that the transactions should remain in log until all the records are written to disk on mirror server. If the server of mirror instance falls behind the server of principal instance then, the amount of active log space will grow. In such a case, user needs to stop the mirroring of database by taking a backup of log, which truncated the log, apply that log backup to database of mirror, and start the mirroring again.

File Shrinking

After identifying the issue, user is able to truncate the log file, that user needs to shrink the file back to the manageable size. User must avoid file shrinking on consistent basis that can lead to issues of fragmentation. However, they have performed log truncation and requires log file to be smaller. Then user can also do it via a management studio with right click of database, selecting all tasks, shrink, and then select files or database. It can also be done by using TSQL and running mentioned commands. For this, user needs to pass to DBCC SHRINKFILE command.

SELECT name
FROM sys.databases_files
WHERE type_desc='LOG'

Once they get the log file name then, they can use DBCC command to shrink the file as mentioned:

DBCC SHRINKFILE ('Sales_'LOG', 1000)

In addition, the user must check that databases are NOT set to auto-shrink. The databases that are shrink at regular intervals can encounter the problems of real performance.

Conclusion

In the above discussion the problem faced by users of transaction log is too big is discussed. Along with it, the cause and solutions are described. There are various solutions being discussed that makes easy for users to resolve the issue that they are facing.