MS BICOE All about data​: dig, distill, visualize, prognosticate & prescribe

In SQL server, there are often different ways to achieve the same goal. This is also true for monitoring the database log size and its usage.

1. DBCC SQLPERF(LOGSPACE)

Typically, we use DBCC SQLPERF(LOGSPACE) to monitor the log size and percent of space used for all databases. This script is very simple and straightforward to use. However, if you want to progmatically use the information output by the DBCC command, then there is a little bit work to do. The script below is one example which can give you the log space information programtically by using DBCC SQLPERF(LOGSPACE):

Declare @LogUsage Table (
							Database_Name sysname
							, Log_Size_MB Float
							, Log_Used_Percent Decimal(8,5)
							, Status tinyint
						);

Insert Into @LogUsage
Exec ('DBCC SQLPERF (LOGSPACE);');

Select	Database_Name
		, Log_Size_MB
		, Log_Size_MB * Log_Used_Percent / 100 As Log_Used_Size_MB
		, Log_Used_Percent
From	@LogUsage

2. sys.dm_os_performance_counters  

The second and less commonly used technique to monitor the log space usage is to use the sys.dm_os_performance_counters dynamic management view. This view keeps the records of the log file size and also data file size as counter name for all databases. As I only concern the log file size for this purpose, I use the script below to monitor the log space usage:

Select		Database_Name
			, [Log File(s) Size (KB)] / 1024.0 As Log_Size_MB
			, [Log File(s) Used Size (KB)] / 1024.0 As Log_Used_Size_MB
			,  ([Log File(s) Used Size (KB)] * 1.0 / [Log File(s) Size (KB)]) * 100 As Log_Used_Percent
From
(
select	instance_Name as Database_Name, counter_name, cntr_value as Log_Size
From	sys.dm_os_performance_counters
Where	counter_name in		(
								'Log File(s) Size (KB)'
								, 'Log File(s) Used Size (KB)'
							)     
			And
		instance_name not in	(
									'mssqlsystemresource'
									, '_Total'
								)  
) X
Pivot	(	Max(Log_Size)
			For Counter_Name 
			In
				(
					[Log File(s) Size (KB)]
					, [Log File(s) Used Size (KB)]
				)  
		) myPiv

If you only concern one database then you can limit the database name in instance_name. Although I calculated the log usage percent, it has indeed a count name 'Percent Log Used' for the sys.dm_os_performance_counters view, but it has no decimal points for the percent value. 

 3. sys.database_files

 The sys.database_files catalog view has information for data files and log file of the current refferenced database. This is different with the preceding 2 ways that can return back the log information for all databases.  As there is no space used information in this view, we need to use the fileproperty function to get the log spaced used value as below:

Select	DB_Name()
		, size / 128.0 As Log_Size_MB
		, FILEPROPERTY(name, 'spaceused') / 128.0 As Log_Used_Size_MB
		, (FILEPROPERTY(name, 'spaceused') * 1.0 / size) * 100 As Log_Used_Percent
From	sys.database_files
Where	type_desc = 'LOG'

Although this script is for log space usage only, it can also be used to monitoring data files size by taking off the where clause.

4. sys.dm_db_log_space_usage (SQL 2012 only)

This dynamic management view is not even documented in the BOL, but this is very simple view. As the view return back the log size in bytes, it may be more useful if converting the bytes to MBs as below:

Select	DB_Name(Database_id)
		, total_log_size_in_bytes / 1024 / 1024.0 As Log_Size_MB
		, used_log_space_in_bytes / 1024 / 1024.0 As Log_Used_Size_MB
		, used_log_space_in_percent AS Log_Used_Percent
From		sys.dm_db_log_space_usage

Given that, this view is so simple I believe it will be easily adopted. However, this view only returns the log space statistics for the current database only.

In SQL server 2012, there is indeed another DMV addded for the data file space as well which is: sys.dm_db_file_space_usage.