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…