Database Experts:Microsoft SQL Server 2005/2008 R2:Get alert when SQL Server Service goes down

 SQL Server Alert Message When Server Goes Down.

It is very obvious to monitor our sql server database services.Sometimes when we are not on server to look the status of services but need to know its status about when it restarted or failed.

One tricky way is to schedule on Database Jobs to monitor on it.

For this purpose follow some small steps.

1.Create One Stored Procedure which calls when services restarted.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
/******************************************************************************/     
/* Copyright: 2012 Database Experts
*      
* Name     : Archive: [SQL_Server_Restart_Mail] $     
* Version    : Revision:      
* Last Updated   : Modtime:      
* Created By   : $Author: Bhavesh Thakkar 
* Modified By   :   
* Script      : This Procedure is developed for Email When SQL Server Restart. 
* Input: Nothing 
* Output: Nothing 
* Version Info:     
*   No.   Author   Date    Comments     
*   ---   ------   ----    --------     
*   1 *   Bhavesh Thakkar   2012-08-30                         
*/     
/*****************************************************************************/  
 
 
CREATE PROCEDURE [dbo].[SQL_Server_Restart_Mail]  
 
as 
 
BEGIN 
 
Declare   
 
  @Subject varchar(1000), 
  @ProfileName varchar(100), 
  @Header varchar(5000), 
  @msg varchar(MAX), 
  @body_format varchar(10), 
  @SQL varchar(5000),
  @to varchar(100), 
  @cc varchar(100),
  @bcc varchar(100)
 
 
 
SET @to='dbexprt@dbexprt.com' 

SET @cc='' 

SET @bcc=''

SET @Subject='[Automated Mail] SQL Server Service Restarted' 
 
SET @ProfileName='Database Mail' 
 
SET @body_format='HTML' 

SET  @msg='This is informational message about SQL Server Service restart.
           This mail is automated generated by sql jobs.It also related to SQL Server Agent Service Restart.'

SET @sql='USE msdb '+
         'EXEC sp_send_dbmail @profile_name='''+@ProfileName+''',
         @recipients='''+@to+''',
         @copy_recipients='''+@cc+''',
         @blind_copy_recipients='''+@bcc+''',
         @subject='''+@Subject+''',
         @body='''+@msg +''',
         @body_format ='''+@body_format+''

    EXEC(@sql) 
  
 
END 
 
 2.Create one database job which execute when sql server agent service restarted with database service restarted.

For this

a.go to SQL Server Management studio -->SQL Server agent-->Jobs--New Jobs



b.



 
c.


 d.Put Stored Procedure name into TSQL field.

 


 e.Parse the sql.

f.



 g.
Go to Schedule(More focus on it).
Schedule Type should be  "Start automatically when SQL Server Agent starts"


Click Ok to Complete.

3.Verify it by Restart Server.