RSS

Category Archives: Database Alert

Customized Database-Server Alert Part#2

Hi my followers today i will complete on the series of the SQL Server Customized Database-Server Alert as i started the first post Part#1 and i explained on it on of the most important Database alert (SQL Server Database alert) and i do one Stored procedure to monitor the database status and to send Immediate alert and today i will add two new alerts :

Part#1 

Read_only Database Report : 

USE msdb
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

— =============================================
— Author: Mustafa EL.Masry
— Create date: 16/09/2015 01:52 PM
— Description: Report by all Database are in read_only Mode
— We are Exceulded the Drive Name E
— =============================================
Create Proc [dbo].[ReadOnlyDatabasesReport]
@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’Databases in Read_Only Mode on DB Cluster ‘ + ( CAST((SELECT SERVERPROPERTY(‘ServerName’)) AS nvarchar))

—Send the mail as table Formate
if(select count(*) from sys.databases where is_read_only <> 0 )>0
Begin
DECLARE @table NVARCHAR(MAX) ;
SET @table =
N'<H2 style=” color: red; ” >Read_only Mode Databases Report</H2>’ +
N’ <span style=” font-size: 16px;” >Urgnet this list by Databases are in the Read_only mode please take immediate action to fix it or Exclude it from the Alert </span>’ +
N'<table border=”1″>’ +
N'<tr><th>Database Name</th><th>Database Status</th></tr>’ +
CAST ( ( Select td=name, ”,td=is_read_only from sys.databases where is_read_only <> 0
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 in Read_Write Mode’

END

Read the rest of this entry »

 
1 Comment

Posted by on September 18, 2015 in Database Alert

 

Tags: , , , , ,

Customized Database-Server Alert Part#1

 

Problem 

AlertHello 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

Read the rest of this entry »

 
1 Comment

Posted by on September 17, 2015 in Database Alert

 

Tags: , , , , , , , ,