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