Proactive Monitor of System Database Files

Dear Readers,

Today I am writing about a very common problem of Outages that are caused by system database data/log files growth.

Problem: generally, we face this issue when system database file is filled up and it can impact to Business an unexpected downtime. This applies to those environments where monitoring/alerting mechanism is not configured properly or not configured at all. As a DBA, I would create my own solution in such environments.

Resolution: In this scenario I will be going to implement the proactive monitoring on system database with fatal error. It does help out if any system database is growing rapidly and any issue at database process level. It will provide the notification in advance to work on respective alert. (It has been verified/tested in SQL Server SQL 2005/2008/2008R2 and 2012 version).

Benefits of implementing this:

Proactive Monitoring
Easy to Analysis growth
Avoids unexpected impact to Business
Customization possible.

Step 1: Add the operator name against @EmailOperator

Step 2: This script will create database & log size Alerts for all system databases. The alert trigger value is 80% of max database/log size. It will give also benefit of severity alerts which monitor like Database Integrity Suspect, Table Integrity Suspect, In Database Processes, In Current Process and In Resource.

DECLARE @ReturnCode INT
	,@JobID BINARY (16)
	,@AlertName VARCHAR(150)
	,@jobname VARCHAR(100)
	,@EmailOperator VARCHAR(16)
	,@DatabaseName VARCHAR(50)
	,@SQLString NVARCHAR(100)
	,@DbAlertFreq INT
	,@LogAlertFreq INT
	,@PerfCondition VARCHAR(100)
	,@NotMessage VARCHAR(250)
	,@instype VARCHAR(30)
	,@AlertSize INT
	,@jobnameike VARCHAR(50) 
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
	--USER OPTIONS--
--This script will resize the system databases with values(line 33)--
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
--Name Operator to send Mails for alerts
SET @EmailOperator = '' ----Change operator name here
--Set Database Alert frequency in minutes
SET @DbAlertFreq = 720 --minutes (720minutes = 12 hours)
--Set log size alert frequency in minutes
SET @LogAlertFreq = 720 --minutes

--********************************************************************---  End of User Options        --
--*******************************************************-
PRINT '************************PLEASE READ AND NOTE*************************************'
PRINT 'If you get errors delete all the Alerts that were created, deallocate the cursor'
PRINT ' by running the following "DEALLOCATE DbName_cursor" then rerun the script again'
PRINT '***********************************************************************'

--Resize the system databases
ALTER DATABASE master modify FILE (
	NAME = master
	,maxsize = 51 MB
	,FILEGROWTH = 10 MB
	)

ALTER DATABASE master modify FILE (
	NAME = mastlog
	,maxsize = 11 MB
	,FILEGROWTH = 5 MB
	)

ALTER DATABASE msdb modify FILE (
	NAME = MSDBData
	,maxsize = 101 MB
	,FILEGROWTH = 10 MB
	)

ALTER DATABASE msdb modify FILE (
	NAME = MSDBLog
	,maxsize = 35 MB
	,FILEGROWTH = 5 MB
	)

ALTER DATABASE tempdb modify FILE (
	NAME = tempdev
	,SIZE = 2 GB
	,maxsize = 10 GB
	,FILEGROWTH = 250 MB
	)

ALTER DATABASE tempdb modify FILE (
	NAME = templog
	,SIZE = 500 MB
	,maxsize = 2 GB
	,FILEGROWTH = 250 MB
	)

PRINT ''
PRINT 'Resized System databases.'

--Check if this is an instance or default installation.
IF (
		SELECT @@SERVICENAME
		) = 'MSSQLSERVER'
BEGIN
	SET @instype = 'SQLServer'
END
ELSE
BEGIN
	SET @instype = 'MSSQL$' + (
			SELECT @@SERVICENAME
			)
END

--Change alert frequency into seconds
SET @DbAlertFreq = @DbAlertFreq * 60
SET @LogAlertFreq = @LogAlertFreq * 60
SET NOCOUNT ON

DECLARE DbName_cursor SCROLL CURSOR
FOR
SELECT NAME
FROM sys.sysdatabases
WHERE (
		NAME IN (
			'master'
			,'msdb'
			,'tempdb'
			)
		)

OPEN DbName_cursor

FETCH FIRST
FROM DbName_cursor
INTO @DatabaseName

WHILE (@@FETCH_STATUS <> - 1)
BEGIN
	IF (@@FETCH_STATUS <> - 2)
	BEGIN
		--create system database size Alerts
		SET @AlertName = @DatabaseName + ' Size Alert'

		--Get database max database size and set alert size to 80%
		CREATE TABLE #temptable1 (AlertSize INT)

		SET @SQLString = N'SELECT ((SUM(maxsize)*8)*0.8) FROM ' + @DatabaseName + '..sysfiles where groupid>0'

		INSERT INTO #temptable1
		EXEC sp_executesql @SQLString

		SET @AlertSize = (
				SELECT AlertSize
				FROM #temptable1
				)

		DROP TABLE #temptable1

		SET @PerfCondition = @instype + ':Databases|Data File(s) Size (KB)|' + @DatabaseName + '|>|' + CAST(@AlertSize AS VARCHAR(15))
		SET @NotMessage = 'This Alert indicates the database "' + @DatabaseName + '" has grown to 80% of its maximum size limit. Please check growth levels, restrictions and disk space. Please remeber to amend this alert if any changes to the database is made.'

		EXECUTE msdb.dbo.sp_add_alert @name = @AlertName
			,@message_id = 0
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = @DbAlertFreq
			,@notification_message = @NotMessage
			,@performance_condition = @PerfCondition
			,@include_event_description_in = 5
			,@category_name = N'[Uncategorized]'

		EXECUTE msdb.dbo.sp_add_notification @alert_name = @AlertName
			,@operator_name = @EmailOperator
			,@notification_method = 1

		PRINT ''
		PRINT 'Created Alert - "' + @AlertName + '" with an alert trigger value of >' + CAST(@AlertSize AS VARCHAR(15)) + 'kb'

		--create system database log size alert
		SET @AlertName = @DatabaseName + ' Log Size Alert'

		--Get database max log size and set alert size to 80%
		CREATE TABLE #temptable2 (AlertSize INT)

		SET @SQLString = N'SELECT ((SUM(maxsize)*8)*0.8) FROM ' + @DatabaseName + '..sysfiles where groupid=0'

		INSERT INTO #temptable2
		EXEC sp_executesql @SQLString

		SET @AlertSize = (
				SELECT AlertSize
				FROM #temptable2
				)

		DROP TABLE #temptable2

		SET @PerfCondition = @instype + ':Databases|Log File(s) Used Size (KB)|' + @DatabaseName + '|>|' + CAST(@AlertSize AS VARCHAR(15))
		SET @NotMessage = 'This Alert indicates the database "' + @DatabaseName + '" log is currently using 80% of its maximum size limit. Please check growth levels, restrictions and disk space. Please remeber to amend this alert if any changes to the database is made.'

		--Check if logging is on, if so add execute log backup to alert
		IF (
				SELECT convert(SYSNAME, DatabasePropertyEx(@DatabaseName, 'Recovery'))
				) = 'FULL'
		BEGIN
			--Get the name of job to execute
			SET @jobnameike = '%' + @DatabaseName + ' Adhoc log backup%'

			IF (
					EXISTS (
						SELECT NAME
						FROM msdb..sysjobs
						WHERE NAME LIKE @jobnameike
						)
					)
			BEGIN
				SET @jobname = (
						SELECT NAME
						FROM msdb..sysjobs
						WHERE NAME LIKE @jobnameike
						)

				EXECUTE msdb.dbo.sp_add_alert @name = @AlertName
					,@message_id = 0
					,@severity = 0
					,@enabled = 1
					,@delay_between_responses = @LogAlertFreq
					,@notification_message = @NotMessage
					,@performance_condition = @PerfCondition
					,@include_event_description_in = 5
					,@job_name = @jobname
					,@category_name = N'[Uncategorized]'

				EXECUTE msdb.dbo.sp_add_notification @alert_name = @AlertName
					,@operator_name = @EmailOperator
					,@notification_method = 1

				PRINT ''
				PRINT 'Created Alert - "' + @AlertName + '" with an alert trigger value of >' + CAST(@AlertSize AS VARCHAR(15)) + 'kb'
				PRINT '  Added job - "' + @jobname + '" to be executed on alert "' + @AlertName + '"'
				PRINT '   *** Note  If the Job name is incorect please amend it manually on the alert ***'
			END
			ELSE
			BEGIN
				EXECUTE msdb.dbo.sp_add_alert @name = @AlertName
					,@message_id = 0
					,@severity = 0
					,@enabled = 1
					,@delay_between_responses = @LogAlertFreq
					,@notification_message = @NotMessage
					,@performance_condition = @PerfCondition
					,@include_event_description_in = 5
					,@category_name = N'[Uncategorized]'

				EXECUTE msdb.dbo.sp_add_notification @alert_name = @AlertName
					,@operator_name = @EmailOperator
					,@notification_method = 1

				PRINT ''
				PRINT 'Created Alert - "' + @AlertName + '" with an alert trigger value of >' + CAST(@AlertSize AS VARCHAR(15)) + 'kb'
				PRINT '  ***** Note: Could not find a transaction log backup for this database to add to the alert, *****'
				PRINT '  *****       Please add the log backup job manually to this alert if one exists.     *****'
			END
		END
		ELSE
		BEGIN
			EXECUTE msdb.dbo.sp_add_alert @name = @AlertName
				,@message_id = 0
				,@severity = 0
				,@enabled = 1
				,@delay_between_responses = @LogAlertFreq
				,@notification_message = @NotMessage
				,@performance_condition = @PerfCondition
				,@include_event_description_in = 5
				,@category_name = N'[Uncategorized]'

			EXECUTE msdb.dbo.sp_add_notification @alert_name = @AlertName
				,@operator_name = @EmailOperator
				,@notification_method = 1

			PRINT ''
			PRINT 'Created Alert - "' + @AlertName + '" with an alert trigger value of >' + CAST(@AlertSize AS VARCHAR(15)) + 'kb'
			PRINT '  ** Note: Database Recovery model = Simple, therefore Log backup job not required/added to alert **'
		END
	END

	IF @DatabaseName = 'tempdb'
	BEGIN
		PRINT '  ***NOTE: If the application databases are bigger than 10GB then you may want to resize the temp database and its alerts'
	END

	FETCH NEXT
	FROM DbName_cursor
	INTO @DatabaseName
END

--Create Severity Alerts
PRINT ''
PRINT 'Creating Severity Alerts'
PRINT ''

EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 19 Errors - Fatal Error In Resource'
	,@message_id = 0
	,@severity = 19
	,@enabled = 1
	,@delay_between_responses = 1810
	,@include_event_description_in = 5
	,@category_name = N'[Uncategorized]'

EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 19 Errors - Fatal Error In Resource'
	,@operator_name = @EmailOperator
	,@notification_method = 1

PRINT 'Created Alert - "Sev. 19 Errors - Fatal Error In Resource"'

EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 20 Errors - Fatal Error In Current Process'
	,@message_id = 0
	,@severity = 20
	,@enabled = 1
	,@delay_between_responses = 1810
	,@include_event_description_in = 5
	,@category_name = N'[Uncategorized]'

EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 20 Errors - Fatal Error In Current Process'
	,@operator_name = @EmailOperator
	,@notification_method = 1

PRINT 'Created Alert - "Sev. 20 Errors - Fatal Error In Current Process"'

EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 21 Errors - Fatal Error In Database Processes'
	,@message_id = 0
	,@severity = 21
	,@enabled = 1
	,@delay_between_responses = 1810
	,@include_event_description_in = 5
	,@category_name = N'[Uncategorized]'

EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 21 Errors - Fatal Error In Database Processes'
	,@operator_name = @EmailOperator
	,@notification_method = 1

PRINT 'Created Alert - "Sev. 21 Errors - Fatal Error In Database Processes"'

EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 22 Errors - Fatal Error: Table Integrity Suspect'
	,@message_id = 0
	,@severity = 22
	,@enabled = 1
	,@delay_between_responses = 1810
	,@include_event_description_in = 5
	,@category_name = N'[Uncategorized]'

EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 22 Errors - Fatal Error: Table Integrity Suspect'
	,@operator_name = @EmailOperator
	,@notification_method = 1

PRINT 'Created Alert - "Sev. 22 Errors - Fatal Error: Table Integrity Suspect"'

EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 23 Errors - Fatal Error: Database Integrity Suspect'
	,@message_id = 0
	,@severity = 23
	,@enabled = 1
	,@delay_between_responses = 1810
	,@include_event_description_in = 5
	,@category_name = N'[Uncategorized]'

EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 23 Errors - Fatal Error: Database Integrity Suspect'
	,@operator_name = @EmailOperator
	,@notification_method = 1

PRINT 'Created Alert - "Sev. 23 Errors - Fatal Error: Database Integrity Suspect"'

EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 24 Errors - Fatal Error: Hardware Error'
	,@message_id = 0
	,@severity = 24
	,@enabled = 1
	,@delay_between_responses = 1810
	,@include_event_description_in = 5
	,@category_name = N'[Uncategorized]'

EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 24 Errors - Fatal Error: Hardware Error'
	,@operator_name = @EmailOperator
	,@notification_method = 1

PRINT 'Created Alert - "Sev. 24 Errors - Fatal Error: Hardware Error"'

EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 25 Errors - Fatal Error'
	,@message_id = 0
	,@severity = 25
	,@enabled = 1
	,@delay_between_responses = 1810
	,@include_event_description_in = 5
	,@category_name = N'[Uncategorized]'

EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 25 Errors - Fatal Error'
	,@operator_name = @EmailOperator
	,@notification_method = 1

PRINT 'Created Alert - "Sev. 25 Errors - Fatal Error"'
PRINT ''
PRINT '***********************************************************************'
PRINT 'All the Alerts have been created, you can go and ammend the alerts as you like.'
PRINT 'Thankyou for using this script, if you find/have any issues, have improved '
PRINT 'the script, have a better script please email amitadmin@gmail.com   '
PRINT '************************************************************************'

CLOSE DbName_cursor

DEALLOCATE DbName_cursor

I hope this helps, please ensure you test/try any script in lower environment.