Monitor SQL Server Transaction Log File Free Space

USE [MSSQLTips] /* If possible, use dedicated administrative database */
GO

-- Create log file monitoring procedure
IF OBJECT_ID('[dbo].[MonitorLogFiles]', 'P') IS NULL
EXEC('CREATE PROCEDURE [dbo].[MonitorLogFiles] AS SELECT 0')
GO

/****************************************************************************************
* Author: Mike Eastland *
* *
* Purpose: This procedure will check the local instance for databases running low *
* on available transaction log file space. *
****************************************************************************************/
ALTER PROCEDURE [dbo].[MonitorLogFiles] (
@db_name VARCHAR(128) = NULL,
/* Name of specific database to analyze */
@debug_mode BIT = 0,
/* Determines if alerts are sent or just printed */
@growth_cnt INT = 2,
/* Minimum number of file growth operations */
@log_pct FLOAT = 80.00
/* transaction log percent full threshold */
)
AS

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @cmd NVARCHAR(MAX),
@db VARCHAR(128),
@list VARCHAR(MAX),
@msg VARCHAR(MAX),
@sev TINYINT,
@used DECIMAL(5,2);

-- Default parameter values
SELECT @cmd = 'DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS',
@growth_cnt = CASE @growth_cnt WHEN 0 THEN 1 ELSE @growth_cnt END,
@sev = CASE @debug_mode WHEN 1 THEN 0 ELSE 16 END;

-- Parameter validation
IF @db_name IS NOT NULL AND DB_ID(@db_name) IS NULL
BEGIN
SELECT @msg = 'Database [' + @db_name + '] not found on ' + @@SERVERNAME +
'. All databases will be analyzed.', @db_name = NULL;
RAISERROR(@msg, 0, 0) WITH NOWAIT;
END

IF @log_pct > 100 OR (@debug_mode = 0 AND @log_pct < 50)
BEGIN
SELECT @msg = 'Invalid log percent used threshold. Default value of 80 will be used.',
@log_pct = 80;
RAISERROR(@msg, 0, 0) WITH NOWAIT;
END

DECLARE @log_used TABLE (
DatabaseName VARCHAR(128),
LogSize FLOAT,
LogSpaceUsed DECIMAL(5,2),
[Status] INT
);

DECLARE @db_logfiles TABLE (
LogicalName VARCHAR(128),
FileID SMALLINT,
PhysicalName VARCHAR(256),
FileGroupName VARCHAR(128),
FileSize VARCHAR(32),
MaxFileSize VARCHAR(32),
GrowthValue VARCHAR(32),
FileUsage VARCHAR(16)
);

DECLARE @svr_logfiles TABLE (
DatabaseName VARCHAR(128),
LogicalName VARCHAR(128),
PhysicalName VARCHAR(256),
PercentUsed DECIMAL(5,2),
FileSize BIGINT,
MaxFileSize BIGINT,
GrowthValue BIGINT
);

DECLARE @svr_drives TABLE (
DriveLetter CHAR(1),
FreeSpace BIGINT
);

-- Populate temp table with free space of all local drives
INSERT INTO @svr_drives
EXEC [master].dbo.xp_fixeddrives;

-- Convert FreeSpace value from MB to KB
UPDATE @svr_drives SET FreeSpace = FreeSpace * 1024;

INSERT INTO @log_used
EXEC sp_ExecuteSQL @cmd;

DECLARE db_csr CURSOR FAST_FORWARD READ_ONLY FOR
SELECT d.name, l.LogSpaceUsed
FROM [master].sys.databases d
INNER JOIN @log_used l ON d.name = l.DatabaseName
WHERE LOWER(d.name) = LOWER(ISNULL(@db_name, d.name))
AND LOWER(d.name) NOT IN ('model', 'northwind', 'pubs')
AND LOWER(d.name) NOT LIKE 'adventurework%'
AND DATABASEPROPERTYEX(d.name, 'Status') = 'ONLINE'
AND DATABASEPROPERTYEX(d.name, 'Updateability') <> 'READ_ONLY'
ORDER BY d.name;

OPEN db_csr;

WHILE 0 = 0
BEGIN
FETCH NEXT FROM db_csr INTO @db, @used;

IF @@FETCH_STATUS <> 0
BREAK;

DELETE FROM @db_logfiles;

SELECT @cmd = '[' + @db + '].dbo.sp_helpfile';

INSERT INTO @db_logfiles
EXEC sp_ExecuteSQL @cmd;

INSERT INTO @svr_logfiles
SELECT @db [DatabaseName],
LogicalName,
PhysicalName,
@used [PercentUsed],
CONVERT(FLOAT, SUBSTRING(FileSize, 1, (CHARINDEX('KB', FileSize)) - 1)) [FileSize],
CASE MaxFileSize
WHEN 'Unlimited' THEN 2147483648
ELSE CONVERT(FLOAT, SUBSTRING(MaxFileSize, 1, (CHARINDEX('KB', MaxFileSize)) - 1))
END [MaxFileSize],
CASE CHARINDEX('%', GrowthValue)
WHEN 0 THEN CONVERT(FLOAT, SUBSTRING(GrowthValue, 1, (CHARINDEX('KB', GrowthValue)) - 1))
ELSE CONVERT(FLOAT, ((CONVERT(DECIMAL(16, 2), SUBSTRING(GrowthValue, 1, (CHARINDEX('%', GrowthValue)) - 1)) / 100) * (CONVERT(FLOAT, SUBSTRING(FileSize, 1, (CHARINDEX('KB', FileSize)) - 1)))))
END [GrowthValue]
FROM @db_logfiles
WHERE LOWER(FileUsage) = 'log only';
END

CLOSE db_csr;
DEALLOCATE db_csr;

IF @debug_mode = 1
SELECT s.DatabaseName
, s.LogicalName
, s.PhysicalName
, s.PercentUsed
, s.FileSize
, s.MaxFileSize
, s.GrowthValue
FROM @svr_logfiles s
INNER JOIN @log_used l ON l.DatabaseName = s.DatabaseName
ORDER BY l.LogSpaceUsed DESC, s.DatabaseName;

-- Remove databases with sufficient log space available from alert consideration
DELETE s
FROM @svr_logfiles s
INNER JOIN @log_used l ON l.DatabaseName = s.DatabaseName
WHERE CONVERT(INT, l.LogSpaceUsed) < @log_pct;

-- No logs can autogrow
SET @list = NULL;

SELECT @list = COALESCE(@list + ', ', '') + a.DatabaseName
FROM @svr_logfiles a
WHERE ( a.GrowthValue = 0
OR a.FileSize = a.MaxFileSize
OR (a.FileSize + (@growth_cnt * a.GrowthValue) >= a.MaxFileSize))
AND NOT EXISTS ( SELECT *
FROM @svr_logfiles b
WHERE b.DatabaseName = a.DatabaseName
AND b.GrowthValue > 0
AND (b.FileSize + (@growth_cnt * b.GrowthValue)) < b.MaxFileSize )
GROUP BY a.DatabaseName;

IF @list IS NOT NULL
BEGIN
SET @msg = 'The following databases are low on available log space and have no log files with proper configuration or sufficient space to autogrow: ' + @list;
RAISERROR(64003, @sev, 1, @msg) WITH LOG, NOWAIT;
END

-- Check for available log growth space on physical drives
SET @list = NULL;

SELECT @list = COALESCE(@list + ', ', '') + d.DriveLetter
FROM @svr_logfiles f
INNER JOIN @svr_drives d ON d.DriveLetter = UPPER(LEFT(f.PhysicalName, 1))
GROUP BY d.DriveLetter, d.FreeSpace
HAVING d.FreeSpace <= SUM(@growth_cnt * f.GrowthValue)
ORDER BY d.DriveLetter;

SET @msg = 'The following drives on ' + @@SERVERNAME + ' do not have enough free space for all files ' +
'to grow by the current value of their respective AutoGrowth parameter: ' + @list;

IF @msg IS NOT NULL
RAISERROR(64003, @sev, 2, @msg) WITH LOG, NOWAIT;

GO

-- Listing 3
USE [msdb]
GO

-- Declare variables
DECLARE @AlertName NVARCHAR(128),
@DatabaseName NVARCHAR(12),
@DebugMessage VARCHAR(MAX),
@JobCategory NVARCHAR(128),
@JobCmd NVARCHAR(MAX),
@JobDescr NVARCHAR(128),
@JobID BINARY(16),
@JobName NVARCHAR(64),
@JobOper NVARCHAR(32),
@JobStep NVARCHAR(128),
@JobSubSystem NVARCHAR(16),
@ReturnCode BIGINT;

-- Initialize variables
SET @AlertName = N'Transaction Log File Monitor';
SET @DatabaseName = 'MSSQLTips'; /* Set to name of dedicated administrative database on the local instance */
SET @JobCategory = N'Instance Monitor';
SET @JobCmd = 'EXEC dbo.MonitorLogFiles';
SET @JobDescr = N'Check for databases whose transaction logs are nearing capacity.';
SET @JobName = N'Monitor - Log Files';
SET @JobOper = N'sqlDBA'; /* Set to name of preferred SQL Server Agent Operator on the local instance */
SET @JobStep = @JobDescr
SET @JobSubSystem ='TSQL';
SET @ReturnCode = 0;
-- End variable initialization

-- Check for existence of specified operator; use failsafe operator if it doesn't
IF NOT EXISTS (SELECT * FROM dbo.sysoperators WHERE [name] = @JobOper)
BEGIN
SET @DebugMessage = 'Operator [' + @JobOper + '] not found; checking for failsafe operator.';
RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\SQLServerAgent',
N'AlertFailSafeOperator', @JobOper OUTPUT;
END

IF @JobOper IS NULL
BEGIN
SET @DebugMessage = 'No failsafe operator found; Job [' + @JobName +
'] will not be created without notification functionality.';
RAISERROR(@DebugMessage, 8, 0) WITH LOG, NOWAIT;
GOTO QuitWithRollback;
END

-- Create alert associated with custom alert message
IF EXISTS (SELECT * FROM dbo.sysalerts WHERE [name] = @AlertName)
EXEC dbo.sp_delete_alert @name = @AlertName;

EXEC dbo.sp_add_alert @name = @AlertName, @message_id = 64003, @severity = 0, @enabled = 1,
@delay_between_responses = 0, @include_event_description_in = 3;

EXEC dbo.sp_add_notification @alert_name = @AlertName, @operator_name = @JobOper,
@notification_method = 1;

-- Create SQL Server Agent job
BEGIN TRANSACTION

-- Create job category if it doesn't already exist
IF NOT EXISTS (SELECT [name] FROM dbo.syscategories WHERE [name] = @JobCategory AND category_class = 1)
BEGIN
EXEC @ReturnCode = dbo.sp_add_category @class = N'JOB', @type = N'LOCAL', @name = @JobCategory;

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
SET @DebugMessage = 'Error creating job category [' + @JobCategory + ']';
RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;
GOTO QuitWithRollback;
END
END

-- Delete job if it already exists
IF EXISTS (SELECT job_id FROM dbo.sysjobs_view WHERE [name] = @JobName)
EXEC dbo.sp_delete_job @job_name = @JobName, @delete_unused_schedule = 1;

-- Create data file monitoring job
EXEC @ReturnCode = dbo.sp_add_job @job_name = @JobName, @enabled = 1, @notify_level_eventlog = 2,
@notify_level_email = 2, @notify_level_netsend = 0, @notify_level_page = 0,
@notify_email_operator_name = @JobOper, @delete_level = 0, @description = @JobDescr,
@category_name = @JobCategory, @owner_login_name = N'sa', @job_id = @JobID OUTPUT;

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
SET @DebugMessage = 'Error creating job [' + @JobName + ']';
RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;
GOTO QuitWithRollback;
END

-- Add step to job
EXEC @ReturnCode = dbo.sp_add_jobstep @job_id = @JobID, @step_name = @JobStep, @step_id = 1,
@cmdexec_success_code = 0, @on_success_action = 1, @on_success_step_id = 0,
@on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0,
@os_run_priority = 0, @subsystem = @JobSubSystem, @command = @JobCmd,
@database_name = @DatabaseName, @flags = 0;

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
SET @DebugMessage = 'Error creating job step [' + @JobStep + ']';
RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;
GOTO QuitWithRollback;
END

-- Explicitly set step id on which job will start
EXEC @ReturnCode = dbo.sp_update_job @job_id = @JobID, @start_step_id = 1;

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
SET @DebugMessage = 'Error setting start step for job [' + @JobName + ']';
RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;
GOTO QuitWithRollback;
END

-- Create job schedule
EXEC @ReturnCode = dbo.sp_add_jobschedule @job_id = @JobID, @name = @JobName, @enabled = 1, @freq_type = 4,
@freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 15, @freq_relative_interval = 0,
@freq_recurrence_factor = 0, @active_start_date = 19900101, @active_end_date = 99991231,
@active_start_time = 1200, @active_end_time = 235959;

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
SET @DebugMessage = 'Error creating job schedule [' + @JobName + ']';
RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;
GOTO QuitWithRollback;
END

-- Designate server for job execution
EXEC @ReturnCode = dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)';

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
SET @DebugMessage = 'Error setting job server for job [' + @JobName + ']';
RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;
GOTO QuitWithRollback;
END

-- Commit changes if no errors occur
IF @@TRANCOUNT <> 0
COMMIT TRANSACTION;

SET @DebugMessage = @JobName + ' job has been (re)created.';
RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;

-- Rollback changes if error occurs at any point in script
QuitWithRollback:
IF @@TRANCOUNT <> 0
ROLLBACK TRANSACTION;

GO