During my Comprehensive Database Performance Health Check engagement with the client, one of the most common issue found was the huge size of the transaction log file. In this blog, we would learn about useful queries which I use to help my client in fixing Huge Transaction Log (LDF) File.
Here are the three most common queries which I use when I am trying to fix an issue where LDF file is huge as compared to MDF file.
- DBCC SQLPERF(LOGSPACE)
- select name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc from sys.databases
- DBCC LOGINFO
Let’s understand each query and its usage.
As per books online – it returns the current size of the transaction log and the percentage of log space used for each database. Use this information to monitor the amount of space used in a transaction log.
We can use below query to find the recovery model of each database and find the cause of not able to truncate the log file (log_reuse_wait_desc) column.
SELECT name ,recovery_model_desc ,log_reuse_wait ,log_reuse_wait_desc FROM sys.databases
Returns virtual log file (VLF) information of the transaction log. Note all transaction log files are combined in the table output. Each row in the output represents a VLF in the t-log and provides information relevant to that VLF in the log.
Above is taken from the documentation of dm_db_log_info. Here is the query we should use after SQL 2016.
SELECT * FROM sys.dm_db_log_info(db_id())
Else we can use DBCC LOGINFO
Using the above three, I get an understanding of cause and possible solution to get free space.
Here is the query which you can copy paste when I get engaged with you or you are trying to fix the space issue. (Change the Database Name)
USE Database_Name_Here GO DBCC SQLPERF(LOGSPACE) GO DBCC LOGINFO GO select * from sys.dm_db_log_info(db_id()) GO select name ,recovery_model_desc ,log_reuse_wait ,log_reuse_wait_desc from sys.databases where database_id = db_id()
Please let me know if the above queries are useful to find out why transaction log is huge.
Reference: Pinal Dave (https://blog.SQLAuthority.com)