View log usage in SQL2005 using Dynamic Management objects

Submitted by Vadim Berezniker on April 6, 2007 - 04:00 UTC
topic: 
sql
vadim
mssql

SQL Server 2005 introduces a number of Dynamic Management objects. One of these is dm_os_performance_counters which gives you access to SQL Server Performance Counters that were only available through Performance Monitor. Several of the counters are related to the log so we can see the log usage for databases from within SQL Server.





 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24





SELECT 
	pc1.instance_name as 'Database', 
	pc1.cntr_value as 'Used', 
	pc2.cntr_value as 'Size',
	CASE 
		WHEN pc2.cntr_value = 0 
		THEN -1 
		ELSE CAST(pc1.cntr_value*100.0 / pc2.cntr_value as int)
	END as '% Used'
FROM
	sys.dm_os_performance_counters pc1
JOIN
	sys.dm_os_performance_counters pc2
ON
	pc1.instance_name = pc2.instance_name
WHERE 
	pc1.object_name = 'SQLServer:Databases'
AND
	pc1.counter_name = 'Log File(s) Used Size (KB)'
AND
	pc2.counter_name = 'Log File(S) Size (KB)'
ORDER BY 
	pc1.instance_name

Log usage % is actually available as a separate counter, but I prefer a simple calculation more than a separate join.

Comments