Customized Database-Server Alert Part#1

 

Problem 

Hello my Followers as we are working as Database administrators (DBA) we can’t stay on our office 24 hour we should enjoy and by the other things in the life but we still responsible about the stability of Production databases should be accessible in any time with the optimal performance so at this time we should leave something working 24 Hour in our servers to monitors the servers and send to us what is happening immoderately. the market have a lot of Product and lot of tools for Monitoring SQL Server from IO , CPU , backup , Security and Database Performance but not all the organization have the capital to provide you all of this tools at this time you should depend on your self because you are still the Responsible person In front of all the management so in this post and other posts i will publish very important and impressive Customized Database Alert depended on T-SQL (DMV Alert ) also i write in the SQL Server alert type or category and How we can configure it .

 

Part#1  , Part#2

SQL Server Database alert Type :

1. Warning Alert
2. Critical Alert
3. OS Alert
4. Customized Database alert (DMV_Alert) this point i will start with it because it is not easy like the previous points it is need some one have good Experience in T-SQL (handmade alert)

Customized database alert list :

this is List by the Database alerts i will explain it one by one and we will go deeply for each one to know the mechanizm of the Stored procedure and How it work and How we can update on it based on our work environment

• Database status Report
• Read_Only Database Report
• Backup Running Now
• Check backup History
• Check_ServicesStatus_Reporting
• Disabled SQL Server Jobs
• Disk Space Alert
• SQLServerRestartAlert_1
• SQLServerRestartAlert_2
• sp_check_log_shipping_monitor_alert
• Trg_TrackLoginManagement (Secuirty Trigger)
• Keep Your DB in Safe Mode (Database Drop/Edit Trigger)
• Database DDL Alert
• Job Status Alert (Job Status Trigger to catch any action happened on the jobs status)

I am not remember all the list i will update it ASAP now let us start by the first DMV Alert but before this we should know

Database Alert Pre-Configuration :

• Configure Database mail (you can configure it by the wizard or by T-SQL )
• Configure database mail operator

Create database Mail Alert :

USE [msdb]
GO

/****** Object: Operator [DBAlert] Script Date: 16/09/2015 09:56:41 PM ******/
EXEC msdb.dbo.sp_add_operator @name=N’DBAlert’,
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@email_address=N’SQLGULF@MostafaElmasry.com’,
@category_name=N'[Uncategorized]’
GO

 

Customized Alert#1 : Database status Report (Download Script here :OfflineDatabasesReport )

This one of the most important alert you should know the Status of the database ( online , Offline , suspected, Recovery , Read only …etc) so in this report we will cover all the status and in the next Alert#2 we will cover the Database in Read_only mode

USE [msdb]
GO

USE msdb
GO
/****** Object: StoredProcedure [dbo].[OfflineDatabasesReport] Script Date: 16/09/2015 10:11:42 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Mustafa EL.Masry
— Create date: 07/06/2015 01:52 PM
— Description: Chkec Database Status
— =============================================
CREATE PROC [dbo].[OfflineDatabasesReport]
@profile_name_P NVARCHAR(500) =’DBMailProfile’,@recipients_P NVARCHAR(500) =’SQLGULF@MostafaElmasry.com’
AS
BEGIN
SET NOCOUNT ON
DECLARE @p_subject NVARCHAR(500)
SET @p_subject = N’Offline Databases Report on DB Cluster ‘ + ( CAST((SELECT SERVERPROPERTY(‘ServerName’)) AS NVARCHAR))

—Send the mail as table Formate
IF(SELECT COUNT(*) FROM sys.databases WHERE state_desc<>’Online’)>0
BEGIN
DECLARE @table NVARCHAR(MAX) ;
SET @table =
N'<H2 style=” color: red; ” >Offline Databases Report</H2>’ +
N’ <span style=” font-size: 16px;” >Urgnet this list by Databases not Accessible please take immediate action to fix it </span>’ +
N'<table border=”1″>’ +
N'<tr><th>Database Name</th><th>Database Status</th></tr>’ +
CAST ( ( SELECT td=name, ”,td=state_desc FROM sys.databases WHERE state_desc<>’Online’
FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>’ ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name=@profile_name_P, –Change to your Profile Name
@recipients=@recipients_P, –Put the email address of those who want to receive the e-mail
@subject = @p_subject ,
@body = @table,
@body_format = ‘HTML’ ;
END
ELSE PRINT ‘All Databases are Online’
END
GO

By this Stored Procedure any database will be under any status Except online status we will receive imminently email sure to receive email you should configure Database mail as i told you before plus you should create scheduled job to run every 10 Second to Execute this Stored procedure

Execute Stored procedure 

USE MSDB

GO

Exec OfflineDatabasesReport ‘DBMailProfile’,’SQLGULF@MostafaElmasry.com’

Email Alert :

Offline Databases Report

Urgnet this is  list by Databases are in the not in the online status please take immediate action to fix it or Exclude it from the Alert

Database Name	Database Status
AMA	OFFLINE

Follow Us :

LinkedIn , Slideshare ,Youtube Channel.MSDN POSTS , WHO WE ARE