Log File Space Issue Monitoring Using SQL Server DMV

, 2017-06-14 (first published: )

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

SELECT 
dbs.[name] AS [Database Name], 
CONVERT(DECIMAL(18,2), dopc1.cntr_value/1024.0) AS [Log Size (MB)], 
CONVERT(DECIMAL(18,2), dopc.cntr_value/1024.0) AS [Log Used (MB)],
CONVERT(DECIMAL(18,2), dopc1.cntr_value/1024.0) - CONVERT(DECIMAL(18,2), dopc.cntr_value/1024.0)[Log Free Space Left (MB)],
CAST(CAST(dopc.cntr_value AS FLOAT) / CAST(dopc1.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log space Used (%)], 
dbs.recovery_model_desc AS [Recovery Model], 
dbs.state_desc [Database State], 
dbs.log_reuse_wait_desc AS [Log Reuse Wait Description]
FROM sys.databases AS dbs WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS dopc  WITH (NOLOCK) ON dbs.name = dopc.instance_name
INNER JOIN sys.dm_os_performance_counters AS dopc1 WITH (NOLOCK) ON dbs.name = dopc1.instance_name
WHERE dopc.counter_name LIKE N'Log File(s) Used Size (KB)%' 
AND dopc1.counter_name LIKE N'Log File(s) Size (KB)%'
AND dopc1.cntr_value > 0 
order by 5 DESC OPTION (RECOMPILE)

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.

The post Log File Space Issue Monitoring Using SQL Server DMV appeared first on .

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads