Home > Backup and Recovery > Proactive Monitoring method Database Log space status to avoid log file full error

Proactive Monitoring method Database Log space status to avoid log file full error


As per the customer requirement we have to make a script that notified us whenever a database (including tempdb) has less than 50% unused transaction log space. We monitored for this condition proactively discover long running, transaction log consuming queries. Such queries can take a long time to rollback and can sometimes be a result of a user error – for example an accidental delete of many records. The DBA at this customer knew he had to take action when log space was low, to investigate such potential user error.

With the monitoring scripts used by us the alarm went off regularly on tempdb. Because of the checkpoint behavior described above, unused transaction log space is only reclaimed at 70% log usage of tempdb. The customer still wanted to headroom of the 50% limit, so he had to be able to distinguish between the “by design” checkpoint at 70% and a troublemaking, long running transaction preventing log space from being reclaimed.

The solution for the customer was to monitor to the column log_reuse_wait_desc in sys.databases in combination with the unused transaction log space. If this column has the value different than CHECKPOINT and NOTHING, the log usage is a result of a long running log space consuming transaction, in tempdb.

The script below illustrates how this is done:

SELECT name
, db.log_reuse_wait_desc
, ls.cntr_value AS size_kb
, lu.cntr_value AS used_kb
, CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)
AS used_percent
, CASE WHEN CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) > .5 THEN
CASE
/* tempdb special monitoring */
WHEN db.name = ‘tempdb’
AND log_reuse_wait_desc NOT IN (‘CHECKPOINT’, ‘NOTHING’) THEN ‘WARNING’
/* all other databases, monitor foor the 50% fill case */
WHEN db.name <> ‘tempdb’ THEN ‘WARNING’
ELSE ‘OK’
END
ELSE ‘OK’ END
AS log_status
FROM sys.databases db
JOIN sys.dm_os_performance_counters lu
ON db.name = lu.instance_name
JOIN sys.dm_os_performance_counters ls
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’
AND ls.counter_name LIKE ‘Log File(s) Size (KB)%’

Advertisements
Categories: Backup and Recovery
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: