The DBCC SQLPERF(logspace) is a handy routine to check the transaction log sizes. But it does have some limitations:
- The log file size is in KB. All of our databases are in GB, so I end up unconsciously converting the KB output into GB.
- Saving the data to a table is not convenient.
- It provides the log file size, and the percent used, but the actual amount of data in the log is left as an exercise for the reader.
My first attempt at producing an alternative view of SQLPERF(logspace) just included the log file information from the SQL performance counters.
My colleague Max MA (t) suggested adding counter values for the database data file size. He also suggested replacing a calculated value for percent of log used with a counter value.
I agreed with adding the data file size counter; I didn’t like the log percent used counter value because it is represented as an integer value with a rounded value.
Here is the query, that happens to make a convenient view:
[sourcecode language='sql' padlinenumbers='true'] CREATE VIEW dbo.v_GetDBSize AS SELECT DatabaseName , [Data File(s) Size (KB)] AS [Data File(s) Size (GB)] , [Log File(s) Size (KB)] AS [Log File(s) Size (GB)] , [Log File(s) Used Size (KB)] AS [Log File(s) Used Size (GB)] , CAST(CASE WHEN [Log File(s) Size (KB)] = 0.00 THEN 0.00 ELSE [Log File(s) Used Size (KB)] / [Log File(s) Size (KB)] * 100.0 END AS NUMERIC(18, 2)) AS [Log % Used] FROM ( SELECT instance_name as DatabaseName , ltrim(rtrim(counter_name)) as counter_name , CAST(cntr_value / 1024. / 1024. AS NUMERIC(18, 2)) AS [Storage(GB)] FROM sys.dm_os_performance_counters WHERE counter_name IN ('Data File(s) Size (KB)' , 'Log File(s) Size (KB)' , 'Log File(s) Used Size (KB)') ) as SourceTable PIVOT ( SUM ([Storage(GB)]) FOR counter_name IN ([Data File(s) Size (KB)] , [Log File(s) Size (KB)] , [Log File(s) Used Size (KB)] ) ) AS PivotTable; [/sourcecode]
Here is the sample output:
Since the code always runs off the blog page, here is a readable version of it:
This is simple stuff but makes checking log files and data file growth convenient. This could easily be used as part of a regularly collected baselines routine.
Stay curious… Keep learning…