MS-SQL Server 2008 R2 and 2005 Agent Restart Alert Job

MS-SQL Server 2008 R2 and 2005 Agent Restart Alert Job

Create below Store Procedure.

USE [master] GO /****** Object:  StoredProcedure [dbo].[sp_SqlServerRestartNotificationMail]    Script Date: 09/20/2012 23:45:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CREATE   PROC [dbo].[sp_SqlServerRestartNotificationMail]      --SET NOCOUNT ON    AS     DECLARE @msgg AS VARCHAR(100)     DECLARE @servername AS VARCHAR(50)     DECLARE @msg AS VARCHAR(500)       DECLARE @activenode AS VARCHAR(30)         SELECT  @servername = @@servername     SELECT  @activenode = CONVERT(VARCHAR(30), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))       SET @msgg = 'SQL Server Instance ' + @servername + ' has restarted'     SET @msg = 'SQL Server Instance ' + @servername + ' has restarted at '         + CAST(GETDATE() AS VARCHAR(30))         + '. Instance is currently active on ' + @activenode         + '. You will be getting another email in 5 mins with current status of databases. If you did not receive another email in 5mins then please connect to the server and check Sql server agent status / health check. Thanks. '            EXEC msdb.dbo.sp_send_dbmail @profile_name = 'NYU-Support',         @recipients = 'vishalk@aditi.com', @subject = @msgg, @body = @msg     EXEC msdb..sp_start_job 'DB Status after Sql Restart' GO   EXEC sp_procoption N'[dbo].[sp_SqlServerRestartNotificationMail]', 'startup','1' GO

Note: This store Procedure will create under Master Database. 

1.      Job Name: <ServerName>_Server_Status

Job Alert Code:

USE [msdb] GO   /****** Object:  Job [ServerName_Server_Status]    Script Date: 09/20/2012 23:05:30 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/20/2012 23:05:30 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   END   DECLARE @jobId BINARY(16) EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'NYU_Server_Status',             @enabled=1,             @notify_level_eventlog=0,             @notify_level_email=0,             @notify_level_netsend=0,             @notify_level_page=0,             @delete_level=0,             @description=N'NYU_Server_Status',             @category_name=N'[Uncategorized (Local)]',             @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object:  Step [NYU_Alert_Start]    Script Date: 09/20/2012 23:05:30 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'NYU_Alert_Start',             @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=1,             @os_run_priority=0, @subsystem=N'TSQL',             @command=N'Exec sp_procoption N''sp_SqlServerRestartNotificationMail'',''startup'', ''true''',             @database_name=N'master',             @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'RunAsSQLAgentServiceStartSchedule',             @enabled=1,             @freq_type=4,             @freq_interval=1,             @freq_subday_type=1,             @freq_subday_interval=0,             @freq_relative_interval=0,             @freq_recurrence_factor=0,             @active_start_date=20120920,             @active_end_date=99991231,             @active_start_time=223201,             @active_end_time=235959,             @schedule_uid=N'a8240410-145c-459f-99c1-05df5b707256' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback:     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:   GO

 

Alert Script: ServerName_Job_Alert

USE [msdb] GO EXEC msdb.dbo.sp_update_alert @name=N'ServerName_Job_Alert',             @message_id=0,             @severity=1,             @enabled=1,             @delay_between_responses=0,             @include_event_description_in=1,             @database_name=N'',             @notification_message=N'',             @event_description_keyword=N'',             @performance_condition=N'',             @wmi_namespace=N'',             @wmi_query=N'' GO

Database Mail Configuration

Open SSMS window -> Management -> database Mail -> Right Click and Select Configure database Mail

Enter Profile Details:

1. Profile Name: Name of Profile
2. Description: Description of Profile

Click On Add Button -> New Account.

Enter Database Mail Account Details:

1. Account Name: Name of Email Alert account
2. Description: Description about the account.
3. Email Address: Email address which will send alert emails.
4. Display Name: Sender Name it will display in Alert Email.
5. Reply Email: Optional
6. Server Name : Email Server Name/ IP address
7. Port Number(SMTP): SMTP port number (default SMTP port number is 25)
8. SSL: email SSL if you are using secure connection.
9. Authentication Mode: Use window authentication / Basic authentication.
   1. User Name: Login id of alert email account.
   2. Password: Password of alert email account.

After Enter all details click on Next Button

Select Public Profiles and check the Alert account which you want to use.

After check the Profile change Default Profile to Yes.

Click on Next -> Next -> Finish

2.      Job Name: DB Status after SQL Restart

USE [msdb] GO   /****** Object:  Job [DB Status after Sql Restart]    Script Date: 09/21/2012 16:47:31 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT  @ReturnCode = 0 /****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/21/2012 16:47:31 ******/ IF NOT EXISTS ( SELECT  name                 FROM    msdb.dbo.syscategories                 WHERE   name = N'[Uncategorized (Local)]'                         AND category_class = 1 )     BEGIN         EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB',             @type = N'LOCAL', @name = N'[Uncategorized (Local)]'         IF ( @@ERROR <> 0              OR @ReturnCode <> 0            )             GOTO QuitWithRollback     END   DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'DB Status after Sql Restart',     @enabled = 1, @notify_level_eventlog = 2, @notify_level_email = 0,     @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0,     @description = N'No description available.',     @category_name = N'[Uncategorized (Local)]', @owner_login_name = N'sa',     @job_id = @jobId OUTPUT IF ( @@ERROR <> 0      OR @ReturnCode <> 0    )     GOTO QuitWithRollback /****** Object:  Step [wait]    Script Date: 09/21/2012 16:47:31 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,     @step_name = N'wait', @step_id = 1, @cmdexec_success_code = 0,     @on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2,     @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 1,     @os_run_priority = 0, @subsystem = N'TSQL',     @command = N'WAITFOR DELAY ''00:05:00''   -- wait for 5 mins so that all DB''s are recovered ater restart -- ideally it will not take more than a minute ', @database_name = N'master', @flags = 0 IF ( @@ERROR <> 0      OR @ReturnCode <> 0    )     GOTO QuitWithRollback /****** Object:  Step [Check DB Ststus]    Script Date: 09/21/2012 16:47:31 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,     @step_name = N'Check DB Ststus', @step_id = 2, @cmdexec_success_code = 0,     @on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2,     @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 1,     @os_run_priority = 0, @subsystem = N'TSQL',     @command = N'declare @dbstatus varchar(25) declare @Updatability varchar(25) declare @Useraccess varchar(25) declare @db varchar(128)   Set nocount ON   create table #results( dbname varchar(50) NULL, Status varchar(25) null , Updatability varchar(25) null , UserAccess varchar(20) null)   declare dcur cursor local fast_forward for   --following update is  for SLC database servr sql03. select distinct name from master..sysdatabases   --select distinct name from master..sysdatabases /*select CATALOG_NAME from INFORMATION_SCHEMA.SCHEMATA*/   open dcur   fetch next from dcur into @db   while @@fetch_status=0 begin   set @dbstatus = convert(sysname,DatabasePropertyEx(@db,''Status'')) set @Updatability = convert(sysname,DatabasePropertyEx(@db,''Updateability'')) set @Useraccess = convert(sysname,DatabasePropertyEx(@db,''UserAccess''))     insert into #results(dbname,Status,Updatability,UserAccess)  values(@db,@dbstatus,@Updatability,@Useraccess)   fetch next from dcur into @db   end close dcur deallocate dcur     Select * from #results   drop table #results   ', @database_name = N'master', @output_file_name = N'C:\job_output.txt',     @flags = 0 IF ( @@ERROR <> 0      OR @ReturnCode <> 0    )     GOTO QuitWithRollback /****** Object:  Step [Enable Agent XPs]    Script Date: 09/21/2012 16:47:31 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,     @step_name = N'Enable Agent XPs', @step_id = 3, @cmdexec_success_code = 0,     @on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2,     @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 1,     @os_run_priority = 0, @subsystem = N'TSQL',     @command = N'IF (charindex(''2005'', @@version) = 0) -- not found begin EXECUTE sp_configure ''show advanced options'', 1 RECONFIGURE WITH OVERRIDE   EXECUTE sp_configure ''xp_cmdshell'', ''1'' RECONFIGURE WITH OVERRIDE End', @database_name = N'master', @flags = 0 IF ( @@ERROR <> 0      OR @ReturnCode <> 0    )     GOTO QuitWithRollback /****** Object:  Step [send db status]    Script Date: 09/21/2012 16:47:31 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,     @step_name = N'send db status', @step_id = 4, @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 = 1,     @os_run_priority = 0, @subsystem = N'TSQL',     @command = N' DECLARE @msgg as varchar(100) DECLARE @servername as varchar(50) DECLARE @msg as varchar(500)   DECLARE @activenode as varchar(30)       select @servername =@@servername   --Select @activenode=CONVERT(varchar(30), ServerProperty(''ComputerNamePhysicalNetBIOS''))     Set @msgg = ''SQL Server Instance ''  + @servername + '' Database Status''   Set @msg = ''Please find database status on instance '' + @servername + '' . This instance was restarted 5mins back. Kindly connect to server for more details. Thanks. ''        EXEC msdb.dbo.sp_send_dbmail @profile_name=''NYU-Support'', @recipients=''vishalk@aditi.com'', @subject=@msgg, @body=@msg, @file_attachments = ''C:\job_output.txt''', @database_name = N'master',     @flags = 0 IF ( @@ERROR <> 0      OR @ReturnCode <> 0    )     GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF ( @@ERROR <> 0      OR @ReturnCode <> 0    )     GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId,     @name = N'RunAsSQLAgentServiceStartSchedule', @enabled = 1, @freq_type = 4,     @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0,     @freq_relative_interval = 0, @freq_recurrence_factor = 0,     @active_start_date = 20120920, @active_end_date = 99991231,     @active_start_time = 0, @active_end_time = 235959,     @schedule_uid = N'a8240410-145c-459f-99c1-05df5b707256' IF ( @@ERROR <> 0      OR @ReturnCode <> 0    )     GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId,     @server_name = N'(local)' IF ( @@ERROR <> 0      OR @ReturnCode <> 0    )     GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF ( @@TRANCOUNT > 0 )     ROLLBACK TRANSACTION EndSave: GO     3.

Note:

In above code check yellow highlighted points and modify it as per your requirement.

• Output File path: Create one .txt file and give the location of file.
• Receiver Email Address: Email address where email alert will send emails. In case if Instance or server will restart then alert will send to this email address.
• Database Mail Profile Name: Name of Database Profile which you have mentioned.

Every day at 12:00 AM this query will execute and send the alert email along below report.

Result of This Alert

1. Whenever SQL Server Instance will restart/ Stop and Start/ Physical machine will restart then this job will execute and it will send two emails.

A.      First Email which will send immediate restart.

B.      Second Email will send after 5 minutes of interval. Here time interval is configurable.

Note: This email will contain report as an attachment which will provide the status of all databases. If any database is having any issue, we can find out though the report

Sample Report: 

1. Whenever SQL Server Agent will restart / Stop and Start, database Email will send alert email along with status of all databases.