Microsoft Technologies: Monitoring Transactional Log File Space Utilization in SQL Server

Introduction

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 theDatabase 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:-

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

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.

Microsoft Technologies

Pages

Sunday, August 18, 2013

Monitoring Transactional Log File Space Utilization in SQL Server