SQL Server – Transaction Log Usage – Metrics

Background

Reviewing Transaction Log Utilization and wanted to briefly document the SQL Code snippets out on the Internet.

 

Outline

  1. DBCC SQLPERF(logspace)
  2. sys.dm_os_performance_counters
    • Object Name
      • SQLServer:Databases
    • Counter Name
      • Log File(s) Size (KB)
      • Log File(s) Used Size (KB)

Performance Counters

DBCC

DBCC sqlperf

Code

dbcc sqlperf(logspace)
go

Output

DBCCSQLPerf_20180611_1139AM.png

Performance Counters

Performance Counters – “Object Name :- SQLServer:Databases” // “Counter Name :- LogFile”

Code – Tabulated

SELECT
        [object]
		= rtrim(pc1.[object_name])

	, [database]
		= rtrim(pc1.instance_name)

	, [logSizeMB]
		= cast
			(
				pc1.cntr_value/1024.0
					as decimal(30, 2)
			)

	, [logUsedMB]
		= cast
			(
				pc2.cntr_value/1024.0
					as decimal(30, 2)
			)

	, [logAvailMB]
		= cast
			(
				(pc1.cntr_value - pc2.cntr_value)
					/1024.0
					as decimal(30, 2)
			)

	, [percentUtilization]
		= cast
			(
				(

						( pc2.cntr_value*100.00 )
					/ NULLIF(pc1.cntr_value, 0)

				)
				as decimal(10, 2)
			)

FROM   sys.dm_os_performance_counters as pc1

JOIN   sys.dm_os_performance_counters as pc2

	ON   pc1.[instance_name] = pc2.[instance_name]
	AND  pc1.[object_name] = pc2.[object_name]

where ( 1 = 1 )

AND pc1.[object_name] = 'SQLServer:Databases'

AND pc2.[object_name] = 'SQLServer:Databases'

AND pc1.counter_name = 'Log File(s) Size (KB)'

AND pc2.counter_name = 'Log File(s) Used Size (KB)'

AND pc1.instance_name not in
		(
				'_Total'
			, 'mssqlsystemresource'
		)

AND pc1.cntr_value > 0

order by

	[logUsedMB] desc

 

Output

performanceCounter_20180611_1133AM.png

Code – XML

select 

	tblInner.[node]

from
	(

		SELECT
              [object]
			    = rtrim(pc1.[object_name])

			, [database]
				= rtrim(pc1.instance_name)

			, [logSizeMB]
				= cast
					(
						pc1.cntr_value/1024.0
							as decimal(30, 2)
					)

			, [logUsedMB]
				= cast
					(
						pc2.cntr_value/1024.0
							as decimal(30, 2)
					)

			, [logAvailMB]
				= cast
					(
						(pc1.cntr_value - pc2.cntr_value)
							/1024.0
							as decimal(30, 2)
					)

			, [percentUtilization]
				= cast
					(
						(

								( pc2.cntr_value*100.00 )
							/ NULLIF(pc1.cntr_value, 0)

						)
						as decimal(10, 2)
					)

		FROM   sys.dm_os_performance_counters as pc1

		JOIN   sys.dm_os_performance_counters as pc2

			ON   pc1.[instance_name] = pc2.[instance_name]
			AND  pc1.[object_name] = pc2.[object_name]

		where ( 1 = 1 )

		AND pc1.[object_name] = 'SQLServer:Databases'

		AND pc2.[object_name] = 'SQLServer:Databases'

		AND pc1.counter_name = 'Log File(s) Size (KB)'

		AND pc2.counter_name = 'Log File(s) Used Size (KB)'

		AND pc1.instance_name not in
				(
					  '_Total'
					, 'mssqlsystemresource'
				)

		AND pc1.cntr_value > 0

		order by
			[logUsedMB] desc

		for xml
			  PATH('database')
			, root('list')

	) tblInner(node)

Output – XML

performanceCounter_XML_20180611_1137AM

 

References

  1. Microsoft
    • Developer
      • Downloads > Programs > Community > Documentation
        • Is there something like DBCC SQLPERF(LOGSPACE) that will show be a list of the databases and how much free space in each?
          Link