Capture and Store SQL Server Database Log File Sizes
Objective:
This SOP outlines the steps to create a historical log of SQL Server database log file sizes using a SQL Agent Job. This helps in monitoring growth trends and managing disk space effectively.
Step 1: Create the Log Size History Table
Target Database: msdb
USE [msdb];
GO
CREATE TABLE [dbo].[DatabaseLogSizeHistory] (
[DatabaseName] NVARCHAR(128) NULL,
[LogFileName] NVARCHAR(128) NULL,
[SizeMB] DECIMAL(10,2) NULL,
[AsOnDate] DATETIME NULL DEFAULT GETDATE()
);
Purpose:
This table stores the log file size in MB and the timestamp of capture.
Step 2: Create SQL Agent Job
Job Name: CaptureDatabaseLogSize
Category: Database Maintenance
Job Creation Script
USE [msdb];
GO
BEGIN TRANSACTION;
DECLARE @ReturnCode INT = 0;
DECLARE @jobId BINARY(16);
— Create Job Category if it doesn’t exist
IF NOT EXISTS (
SELECT name FROM msdb.dbo.syscategories
WHERE name = N’Database Maintenance’ AND category_class = 1
)
BEGIN
EXEC msdb.dbo.sp_add_category
@class = N’JOB’,
@type = N’LOCAL’,
@name = N’Database Maintenance’;
END
— Create SQL Agent Job
EXEC @ReturnCode = msdb.dbo.sp_add_job
@job_name = N’CaptureDatabaseLogSize’,
@enabled = 1,
@description = N’Captures SQL Server log file size history’,
@category_name = N’Database Maintenance’,
@owner_login_name = N’sa’,
@job_id = @jobId OUTPUT;
— Add Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @jobId,
@step_name = N’CaptureDatabaseLogSize’,
@subsystem = N’TSQL’,
@command = N’
INSERT INTO msdb.dbo.DatabaseLogSizeHistory (DatabaseName, LogFileName, SizeMB, AsOnDate)
SELECT
DB_NAME(mf.database_id),
mf.name,
CAST(mf.size * 8.0 / 1024 AS DECIMAL(10,2)),
GETDATE()
FROM sys.master_files mf
INNER JOIN sys.databases db ON mf.database_id = db.database_id
WHERE mf.type_desc = ”LOG”;’,
@database_name = N’master’,
@output_file_name = N’K:\SQLMPLANLOG\CaptureDatabaseLogSize.txt’,
@on_success_action = 1,
@on_fail_action = 2,
@flags = 6;
— Define Schedule (Every 30 Minutes Daily)
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @jobId,
@name = N’CaptureDatabaseLogSize’,
@enabled = 1,
@freq_type = 4, — Daily
@freq_interval = 1,
@freq_subday_type = 4, — Minutes
@freq_subday_interval = 30,
@active_start_date = 20250425,
@active_start_time = 300;
— Attach Job to Local Server
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @jobId,
@server_name = N'(local)’;
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;
Step 3: Validate the Data
To verify the data collected:
USE [msdb];
GO
SELECT
[DatabaseName],
[LogFileName],
[SizeMB],
[AsOnDate]
FROM msdb.dbo.DatabaseLogSizeHistory
ORDER BY AsOnDate DESC;
Maintenance Tips
- Review this data weekly for unusual log growth patterns.
- Add alerts for abnormally large log sizes if needed.
- Purge or archive old data periodically to manage table size.
