Posted By

tvanzele on 04/16/10


Tagged

sql DMV


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

g8rpal


List of recovery model, log reuse wait description, log file size, log usage size and compatibility level for all databases on i


 / Published in: SQL
 

  1. SELECT db.[name] AS [DATABASE Name], db.recovery_model_desc AS [Recovery Model],
  2. db.log_reuse_wait_desc AS [Log Reuse Wait Description],
  3. ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
  4. CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %],
  5. db.[compatibility_level] AS [DB Compatibility Level], db.page_verify_option_desc AS [Page Verify OPTION]
  6. FROM sys.DATABASES AS db
  7. INNER JOIN sys.dm_os_performance_counters AS lu
  8. ON db.name = lu.instance_name
  9. INNER JOIN sys.dm_os_performance_counters AS ls
  10. ON db.name = ls.instance_name
  11. WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
  12. AND ls.counter_name LIKE 'Log File(s) Size (KB)%';

Report this snippet  

You need to login to post a comment.