Use 4 Different Ways to Monitor Database Log Size and its Usage

April 29, 2012 at 6:02 AMSteven Wang

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.


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

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
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)'
		instance_name not in	(
									, '_Total'
) X
Pivot	(	Max(Log_Size)
			For Counter_Name 
					[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.



Posted in: Database Administration | SQL Server 2012 | Transaction Log | T-SQL

Tags: , , ,

Comments (5) -

  data analysis reporting services        
SQIAR ( is a leading Business Intelligence company.Sqiar Consultants Provide Tableau Software Consultancy To small and Medium size of organization.


United States narcotráfico unibank panama says:

There are some interesting points in time in this article but I don’t know if I see all of them center to heart. There is some validity but I will take hold opinion until I look into it further. Good article , thanks and we want more! Added to FeedBurner as well


SQIAR ( is a leading Business Intelligence company and provides Tableau Software consultancy across United Kingdom,USA,Canada,Australia


I just want to mention I am very new to blogs and certainly liked you're blog. Probably I’m likely to bookmark your blog . You amazingly come with good posts. Thank you for revealing your webpage.


Johnscornor says:

Big thumbs for making amazing blog. This is the best site. Actually this was an awesome blog post. You have a very inspiring way of exploring and sharing your thoughts. I enjoyed reading your stuff.  Thank you !!


Add comment