A DMV a Day – Day 15

The DMV for Day 15 is sys.dm_os_performance_counters, which is described by BOL as:

Returns a row per performance counter maintained by the server. For information about each performance counter, see Using SQL Server Objects.

This DMV is very useful, but can be very frustrating to work with. Depending on the value for cntr_type for a given row, you will have to go through some interesting gyrations to get meaningful information from this DMV. It is a replacement for the old sys.sysperfinfo from SQL Server 2000. This particular DMV works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Recovery model, log reuse wait description, log file size, log usage size 
-- and compatibility level for all databases on instance
SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model], 
db.log_reuse_wait_desc AS [Log Reuse Wait Description], 
ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], 
db.[compatibility_level] AS [DB Compatibility Level], db.page_verify_option_desc AS [Page Verify Option]
FROM sys.databases AS db
INNER JOIN sys.dm_os_performance_counters AS lu 
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS 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)%';

This particular query shows you the recovery model, log reuse wait description, transaction log size, log space used, % of log used, compatibility level, and page verify option for each database on the current SQL Server instance. This is all very valuable information that I like to gather when I am evaluating an unfamiliar database server. It it also useful from a monitoring perspective. For example, if your log reuse wait description is something unusual (such as ACTIVE_TRANSACTION), and your transaction log is 85% full, I would want some alarm bells to be going off…

This entry was posted in SQL Server 2008. Bookmark the permalink.

3 Responses to A DMV a Day – Day 15

  1. Yuri says:

    Hi Glen running your script I got Msg 8134, Level 16, State 1, Line 1Divide by zero error encountered.

  2. How to prevent the disk running out of space because of log files in sql server

    Shrinking the log file script for SQL server database

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s