How to Monitor Transaction Log File Usage in SQL Server

As a Best Practice database administrator should always monitor the space usage within the SQL Server Transaction Log file. This will help you quickly understand how much space the Transaction Log file is using during huge data loads or during routine daily database maintenance activities. The space used by Transaction Log file will depend upon the Database Recovery Model and how frequently the Transaction Log backups are performed. This article explains how to monitor SQL Server transaction log file space usage over a period of time with the help of an SQL Server Agent Job.

How to Monitor Transaction Log File Space Utilization in SQL Server

DBCC SQLPERF (LOGSPACE)

Using DBCC SQLPERF (LOGSPACE) command one can view the transaction log space usage statistics for all databases. Using this command one can also reset wait and latch statistics.

A sample output of DBCC SQLPERF (LOGSPACE) command is shown below:-

Find below the description of each column in the result set:

• Database Name: – Name of the database for which the log statistics is displayed.
• Log Size (MB): – Current size of transaction log file in MB.
• Log Space Used (%): – Percentage of the log file currently occupied with transaction log information.
• Status: – Status of the log file. Always set to 0.

Permissions Required to Execute DBCC SQLPERF (LOGSPACE)

To execute DBCC SQLPERF (LOGSPACE) a user will require VIEW SERVER STATE permission on the server.

Let us go through in details the TSQL code which will help you monitor the Transaction Log space usage.

Quick explanation of the TSQL code

1. Creates a new database by the name DBA
2. Creates a new table by the name MonitorTransactionLogFileUsage within DBA database. 
3. Creates a stored procedure by the name CaptureTransactionLogFileUsage within DBA Database. 
4. Create a SQL Server Agent Job to execute CaptureTransactionLogFileUsage stored procedure. Schedule the job to capture transaction log file utilization once in every 10 minute intervals on a minimum.

Step 1:- Create DBA Database

USE master;
GO

IF DB_ID (N'DBA') IS NOT NULL
	DROP DATABASE DBA;
GO

CREATE DATABASE DBA
GO

Step 2:- Create MonitorTransactionLogFileUsage Table in DBA Database

Use DBA
GO

IF EXISTS 
	(
		SELECT *
		FROM sys.objects
		WHERE object_id = OBJECT_ID(N'[dbo].[MonitorTransactionLogFileUsage]')
		AND TYPE IN (N'U')
	)
	DROP TABLE [dbo].MonitorTransactionLogFileUsage
GO

CREATE TABLE dbo.MonitorTransactionLogFileUsage (
	ID INT IDENTITY(1, 1)
	,DatabaseName SYSNAME
	,LogSizeInMB DECIMAL(18, 5)
	,LogSpaceUsedInPercentage DECIMAL(18, 5)
	,[Status] INT
	,RecordTime DATETIME DEFAULT GETDATE()
	)
GO

Step 3:- Create CaptureTransactionLogFileUsage Stored Procedure in DBA Database to store DBCC SQLPERF (LOGSPACE) results

Use DBA
GO

CREATE PROCEDURE CaptureTransactionLogFileUsage
AS
BEGIN
	INSERT INTO dbo.MonitorTransactionLogFileUsage
		(
		 DatabaseName
		,LogSizeInMB
		,LogSpaceUsedInPercentage
		,[Status]
		)
	EXEC ('DBCC SQLPERF(LOGSPACE)')
END
GO

Step 4:- Capture Transactional Log File Usage

As a best practice a database administrator can create a new SQL Server Agent Job with the below TSQL code and schedule it to run multiple times during the day. However, we recommend you to run the job once every 10 minutes so that you have considerable amount of history to compare the transaction file growth over a certain period of time.

Use DBA
GO

EXEC CaptureTransactionLogFileUsage
GO

Analysing Transaction Log File Usage

Execute the below query to identify the transaction log file usage of Staging database over the last few days.

USE DBA
GO

SELECT 	 ID
	, DatabaseName
	, LogSizeInMB
	, LogSpaceUsedInPercentage
	, RecordTime
FROM MonitorTransactionLogFileUsage
	WHERE (DatabaseName = 'Staging')
ORDER BY RecordTime DESC
GO

Conclusion

DBAs top most priority is to ensure databases are up and running all the time. In order to effectively manage the transaction log growth it is very important to periodically it. Using the code mentioned in this article a DBA can effective monitor the effective growth of TLOG file. We would recommend scheduling the job to run once in every 10 minutes or even lower intervals on busy systems to identify the pattern of TLOG growth.