Advanced Search

Display logfile size and usage via dmv

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. -- Recovery model, log reuse wait description, log file size, log usage size 
  2. -- and compatibility level for all databases on instance
  3. SELECT db.[name] AS [DATABASE Name], db.recovery_model_desc AS [Recovery Model], 
  4. db.log_reuse_wait_desc AS [Log Reuse Wait Description], 
  5. ls.cntr_value AS [Log SIZE (KB)], lu.cntr_value AS [Log Used (KB)],
  6. CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], 
  7. db.[compatibility_level] AS [DB Compatibility Level], db.page_verify_option_desc AS [Page Verify OPTION]
  8. FROM sys.databases AS db
  9. INNER JOIN sys.dm_os_performance_counters AS lu 
  10. ON db.name = lu.instance_name
  11. INNER JOIN sys.dm_os_performance_counters AS ls 
  12. ON db.name = ls.instance_name
  13. WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%' 
  14. AND ls.counter_name LIKE 'Log File(s) Size (KB)%';

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.