MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter
















Latest SQL Server Tips




















































How to Monitor SQL Server Database Transaction Log File Usage

Read Comments


How to Monitor Transactional Log File Space Utilization 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 transactional log file space usage over a period of time with the help of an SQL Server Agent Job.

Overview of 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.

Recommendation:- Configure Auto Growth for Data and Log Files. For more information see, How to Change SQL Server Database Auto Growth Settings

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

DBCC SQLPERF(LOGSPACE)

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 Transactional 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 transactional 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 transactional file growth over a certain period of time.

Use DBA
GO

EXEC CaptureTransactionLogFileUsage
GO

Analysing Transactional Log File Usage

Execute the below query to identify the transactional 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
DBCC SQLPERF (LOGSPACE) Output”

DBA's 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 idenfify the pattern of TLOG growth.





Last Updated On: July 25, 2015



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: