SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Learn Uses of log_reuse_wait_desc In SQL Server

Overview

Many times, while working on SQL Server, the transaction log files frequently grow due to various reasons. Out of which, one such reason is that there is, already existing assigned space of the file in the SQL Server. This can be happened due to increment in the utilization of database. In a way to overcome from such an issue behind the transaction log file for a specific database for not reusing the log space, the log_reuse_wait_desc column that gives a text-based explanation of the blockage in reusing of the transaction log. In the following section, we will discuss the values of log reuse in SQL Server.

Where to use Log_Reuse_Wait_Desc?

SQL Server utilizes an information that is captured in the transaction log for this rollback procedure. Therefore, it cannot be reutilized virtual log files, which covers the all the records of log transactions, which are active. Server will return the value of log_reuse_wait_desc of the Active_Transcation if it is run out of the virtual log. To overcome from this, users have to rollback all the transactions. The safe and secure way is to wait until the transaction is completed. There are various reasons due to which normal transaction can turn into long running transaction. Users may kill its session if the user is unable to afford extra time for long running transaction. However, it will cause a transaction to roll back. Therefore, it is important to keep all the transaction as short as possible. There are some values where log reuse can be utilized as mentioned below:

Log Backup: This is one of the most common values to check whether, you are in the full or bulk logged recovery model and a log backup has not happened. In all these recovery models, backup log makes log truncation. Even you can also backup and restore the active log. It means that there is backup of data running or any other restoration is running. The log cannot be truncated at the time of restore, and is essential for backups of data so that it cannot be truncated.

Database Mirroring: This means that the mirroring database has more or less latency in it and there are some logs records on the principal of mirroring that has not been yet sent to the mirroring mirror. This can occur if the mirror is configured for operation of asynchronous, where the transactions can be committed on the principal before their log records have been sent. It can also happen in mode of synchronous, if the mirror becomes disconnected or the mirroring session is suspended

Replication: This value displays when there is a committed transactions that have not been yet scanned by the transaction replication. Log Reader Agent job for sending to the distributor of replication them for Changing the Data Capture.

Conclusion

There are numerous of reasons due to which the server gives a message to truncate the transaction log due to the growing of log files. In the above discussion, various uses of log_reuse_wait_desc in SQL server are described along with it the proper values of log discussion are mentioned. It helps to give a text-based description of the blockage in reusing the transaction log.

Zora's SQL Tips

Hi! I am Zora Stalin, an IT geek and a passionate learner of technology. Besides my job as an Information Technology Analyst, I love searching and sharing new things that excite me help for others.

Comments

Leave a comment on the original post [sqlserveroverview.blogspot.com, opens in a new window]

Loading comments...