Log File Space Issue Monitoring Using SQL Server DMV

As a continuation of “Database Monitoring using DMV” series, this blog will cover how quickly you can address the log file space related issue. Below are the previous blogs which you may find useful in your everyday job.

To address the log file space related issue, DBAs generally look at two different places;

  • DBCC SQLPERF(LOGSPACE) to check the utilization of the log file by referring the column “Log space used %”
  • sys.databases system view to check the column “Log Reuse Wait Description” to find out the reason – “what activity is causing space issue to the transaction log file?”

The below script will provide you both the information together along with some additional relevant columns

The output of the above query:

Below are the columns definition to have a better understanding of them.

Database Name – It shows the database name
Log Size (MB) – The database Log file size in MB
Log Used (MB) – The database Log file used size in MB
Log Free Space Left (MB) – How much free space left inside the log file to be used
Log Space Used (%) – It indicates the database log file used %age
Recovery Model – It shows the database Recovery model like – Simple, Full, Bulk..etc
Database State –  The shows the database states like Online, Recovering, Suspected..etc
Log Reuse Wait Description – Description of reuse of transaction log space is currently waiting on one of the following; CHECKPOINT, LOG_BACKUP, REPLICATION, DATABASE_MIRRORING, ACTIVE_TRANSACTION…etc

Let me know if you find the query useful and want to add some additional information also in the query.I will publish it on the blog with due credit to you.

Leave a comment

Your email address will not be published. Required fields are marked *