SQL server

Freespace in tempdb transaction log.

Handy script to see how much of the tempdb log is being used:

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
Standard

Thinking about someting? 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