View to get DB Size

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:

image

Since the code always runs off the blog page, here is a readable version of it:

image

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…

0 Comments

Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>