Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Monitor SQL Server Transaction Log File Free Space


By:   |   Read Comments (2)   |   Related Tips: More > Monitoring

Attend these FREE SQL Server 2017 webcasts >> click to register


Problem

There are two kinds of SQL Server DBAs in this world: those who have experienced an outage due to a full transaction log file and those who will. If you're one of the lucky ones who already have, you've likely seen an error message similar to the following:

Msg 9002, Level 17, State 4, Line 7
The transaction log for database 'My_DB' is full due to 'ACTIVE_TRANSACTION'.

This tip is intended to help DBAs identify the potential issue before it leads to an outage.

Solution

My last two monitoring tips covered monitoring free disk space on SQL Server and monitoring SQL Server data files. This tip will cover monitoring space utilization for transaction log files. Similar to the data file monitoring tip, the code listed below will create the necessary components to automate the monitoring and alerting for databases running low on available transaction log space.

Prerequisite Considerations

  • Be sure to configure database mail and SQL Agent notifications as a prerequisite for proper functionality of this monitoring job.
  • This solution assumes that custom error message 64003 is not currently in use by the target instance. If this is not the case, code changes will be necessary.
  • The code has been tested against SQL Server 2012, but should function on version 2005 and greater.

Custom Error Message

Listing 1 creates the custom error message used by the transaction log file monitoring procedure for notifications. If error message 64003 is already in use by the instance, it will be overwritten.

-- Listing 1
USE [master]
GO

-- Create the custom error message
EXEC dbo.sp_addmessage @msgnum = 64003,
@msgtext = 'Transaction Log File Monitor Alert - %s',
@severity = 8, @with_log = 'FALSE', @replace = 'REPLACE'
GO

SQL Server Transaction Log File Monitoring Stored Procedure

The code in listing 2 creates the stored procedure used to check for databases on the local instance which are running low on available transaction log space. It is recommended that this procedure be created in a dedicated, administrative database as opposed to a user or system database.

--Listing 2
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

The procedure uses DBCC SQLPERF (LOGSPACE) to find databases whose transaction log percent space utilization exceeds the value of the @log_pct parameter. It then uses sp_helpfile and xp_fixeddrives to check for two scenarios related to space constraints for transaction log files. The first scenario will check for databases without a transaction log that can autogrow. This scenario can present itself for a given database in the following ways:

1. There are no transaction log files enabled for autogrowth.
2. The current size for all transaction log files is equal to their respective max size.
3. There are no transaction log files that can grow by the number of iterations defined by the @growth_cnt parameter.

The second scenario will check for any local disk on the server that cannot accommodate at least the number of file growth iterations defined by the @growth_cnt parameter for all transaction log files which reside on the disk.

As with most things in the DBA world, your mileage may vary. Be sure to test the procedure in your environment using different parameter combinations. This can be done with the @debug_mode parameter set to 1, which should suppress the alerts.

SQL Server Agent Operator, Alert, and Job

The code in listing 3 targets the SQL Server Agent subsystem to verify and/or create the remaining components required to automate transaction log file monitoring. Specifically, the code performs the following steps:

1. Check for the existence of a SQL Server Agent Operator
2. Create a custom SQL Server Agent Alert for the custom Error Message created by Listing 1.
3. Create a SQL Server Agent Job to execute the transaction log file monitoring procedure created by Listing 2.

Before executing the script in listing 3, be sure to update the values for the @DatabaseName and @JobOper variables to reflect the dedicated administrative database and desired local SQL Server Agent Operator, respectively.

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

Conclusion

The code in this tip provides a way to automate the monitoring of SQL Server transaction log space usage on the local instance for one or more databases using tools and functionality native to SQL Server.

Next Steps
  • Update the job schedule based on the desired monitoring interval. The job is scheduled to run every 15 minutes by default.
  • Determine if the default value of @log_pct (80.0, or 80%) is an acceptable threshold in your environment.
  • Consider additional customization to enable different alert types, such as email or paging, for different percent used thresholds.
  • For more in-depth information about SQL Server transaction logs, review this tip.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Mike Eastland Mike Eastland has been a SQL Server DBA since 1999. His main areas of interest are monitoring and the automation of administrative tasks.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, March 14, 2017 - 1:57:20 PM - Lora Martin Back To Top

 Hi.  This particular item help tremendously.  I have set this up to run on several servers -- logging to a central server and tables.  I have updated the script to log to a table -- so we can track anything.   This helps to monitor everything from SQLSentry and Solarwinds.  Thank you!

 

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[uspMonitorLogFiles]    Script Date: 3/13/2017 2:00:40 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

/****************************************************************************************

* Author: Mike Eastland *

* *

* Purpose: This procedure will check the local instance for databases running low * 

* on available transaction log file space. *

* Lora Martin 3/14/2017 - Update to write to central table table 

*

****************************************************************************************/

ALTER PROCEDURE [dbo].[uspMonitorLogFiles] (

@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

 

 

DECLARE @LogDate datetime

SET @LogDate = getdate()

 

DELETE FROM SQLLINK.master.dbo.Monitor_LogFiles WHERE ServerName = @@SERVERNAME AND LogDate <= dateadd(dd, -30, getdate())

 

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 (

   ServerName VARCHAR(128),

DatabaseName VARCHAR(128),

LogicalName VARCHAR(128),

PhysicalName VARCHAR(256),

PercentUsed DECIMAL(5,2),

FileSize BIGINT,

MaxFileSize BIGINT,

GrowthValue BIGINT,

isHighPercentUsed VARCHAR(1) NULL,

isCritical VARCHAR(1)  NULL,

LogDate datetime,

ErrorMsg VARCHAR(500) NULL

);

 

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', 'master')

--AND LOWER(d.name) NOT LIKE 'netspend%' 

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 (ServerName, DatabaseName, LogicalName, PhysicalName, PercentUsed, FileSize, MaxFileSize, GrowthValue, LogDate)

SELECT 

   @@SERVERNAME,

@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],

@LogDate

FROM @db_logfiles

WHERE LOWER(FileUsage) = 'log only';

END

 

CLOSE db_csr;

DEALLOCATE db_csr;

 

-- Update databases with sufficient log space available from alert consideration

 

UPDATE s SET s.isHighPercentUsed = 

(CASE WHEN (CONVERT(INT, l.LogSpaceUsed) > @log_pct) THEN 'Y'

ELSE 'N'

END)

FROM @svr_logfiles s

JOIN @log_used l ON l.DatabaseName = s.DatabaseName

WHERE s.ServerName = @@SERVERNAME 

AND s.LogDate = @LogDate;

 

UPDATE s  SET s.ErrorMsg = 

(CASE WHEN (CONVERT(INT, l.LogSpaceUsed) > @log_pct) THEN 'The DB is low on available log space and has no log files with proper configuration or sufficient space to autogrow.  '

END)

FROM @svr_logfiles s

JOIN @log_used l ON l.DatabaseName = s.DatabaseName

WHERE s.ServerName = @@SERVERNAME

AND s.LogDate = @LogDate;

 

;WITH drivespace AS (

SELECTsd.driveletter,

sd.FreeSpace,

SUM(@growth_cnt * sl.GrowthValue) updatevalue 

FROM @svr_logfiles sl 

JOIN @svr_drives sd ON sd.driveLetter = UPPER(LEFT(sl.PhysicalName, 1))

WHERE sl.ServerName = @@SERVERNAME

AND sl.LogDate = @LogDate

GROUP BY sd.driveletter, sd.FreeSpace

HAVING sd.freespace <= SUM(@growth_cnt * sl.GrowthValue)

 

UPDATE f SET f.isCritical = 'Y', f.ErrorMsg = 'The drive does not have enough free space for all files to grow by the current value of their respective AutoGrowth parameter.  '

FROM @svr_logfiles f

JOIN drivespace ds ON ds.DriveLetter = UPPER(LEFT(f.PhysicalName, 1))

WHERE (ds.FreeSpace <= ds.updatevalue)

AND f.ServerName = @@SERVERNAME

AND f.isHighPercentUsed = 'Y'

AND f.LogDate = @LogDate;

 

IF @debug_mode = 1

SELECT * from @svr_logfiles

ELSE

INSERT INTO SQLLINK.master.dbo.Monitor_Logfiles (ServerName, DatabaseName, LogicalName, PhysicalName, PercentUsed, FileSize, MaxFileSize, GrowthValue, LogDate, isHighPercentUsed, isCritical, ErrorMsg)

SELECT @@SERVERNAME

, s.DatabaseName

, s.LogicalName

, s.PhysicalName

, s.PercentUsed

, s.FileSize

, s.MaxFileSize

, s.GrowthValue

,@LogDate

,s.isHighPercentUsed

,s.isCritical

,s.ErrorMsg

FROM @svr_logfiles s

INNER JOIN @log_used l ON l.DatabaseName = s.DatabaseName

WHERE s.isHighPercentUsed = 'Y' and s.isCritical = 'Y'

 

 

 


Wednesday, May 13, 2015 - 3:57:47 AM - Thomas Franz Back To Top

very nice script, but you have to add the parameter @lang = 'us_english' to the sp_addmessage call, if you have not an english installation (otherwise it would fail with a message that you first have to add the us_english error message)


Learn more about SQL Server tools