How to get alerted anytime SQL Service is restarted?


You may and should have monitoring in place to monitor state of your servers, services, jobs, critical and not critical errors etc.

Here I just wanted to share a quick script to create a SQL Server agent job to alert you whenever SQL Server service is started/restarted.

If you have CMS setup, you could use it to deploy this across all SQL or most critical instances after tweaking it to meet your requirements




-- For SQL 2005 or up

-- Make sure it is running SQL 2005 or up
IF (@@microsoftversion / 0x1000000) & 0xff >= 9
   PRINT 'This server is running SQL Server 2005 or up.'
ELSE
BEGIN
   RAISERROR('ERROR: This server is running SQL 2000 or older version, exiting...', 16, 1)
   return
END 

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO

 SELECT NEWID()
USE [msdb]
GO
-- IF THE SERVER IS DEV OR TEST, CHANGE THE CRITICAL OPERATO FROM PAGE TO JUST EMAIL
DECLARE @CriticalOperator varchar(500)
IF (@@servername  like '%dev%' or @@servername like '%tst%')
    SET @CriticalOperator = '<youremailaddresshere>'
ELSE
    SET @CriticalOperator = '<yourpageraddresshere'

 
USE [msdb]
IF NOT EXISTS (select * from dbo.sysoperators where name = 'DBA - Critical')
       EXEC msdb.dbo.sp_add_operator @name=N'DBA - Critical',
                     @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=@CriticalOperator,
                     @category_name=N'[Uncategorized]'

IF NOT EXISTS (select * from dbo.sysoperators where name = 'DBA - NonCritical')
       EXEC msdb.dbo.sp_add_operator @name=N'DBA - NonCritical',
                     @enabled=1,
                     @weekday_pager_start_time=80000,
                     @weekday_pager_end_time=180000,
                     @saturday_pager_start_time=80000,
                     @saturday_pager_end_time=180000,
                     @sunday_pager_start_time=80000,
                     @sunday_pager_end_time=180000,
                     @pager_days=62,
                     @email_address=N'<youremailaddresshere>',
                     @category_name=N'[Uncategorized]'

GO

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
IF  not EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DBA - SQL Server Service Start Notification')
BEGIN
       PRINT 'CREATING JOB...'
       EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA - SQL Server Service Start Notification',
                     @enabled=1,
                     @notify_level_eventlog=0,
                     @notify_level_email=3,
                     @notify_level_netsend=0,
                     @notify_level_page=0,
                     @delete_level=0,
                     @description=N'No description available.',
                     @category_name=N'Database Maintenance',
                     @owner_login_name=N'sa',
                     @notify_email_operator_name=N'DBA - Critical', @job_id = @jobId OUTPUT
       IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
       /****** Object:  Step [dummy]    Script Date: 09/29/2010 10:54:23 ******/
       EXEC @ReturnCode = msdb.dbo.sp_add_jobstep  @job_name=N'DBA - SQL Server Service Start Notification', @step_name=N'dummy',
                     @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=0,
                     @os_run_priority=0, @subsystem=N'TSQL',
                     @command=N'select @@version',
                     @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_name=N'DBA - SQL Server Service Start Notification', @name=N'When it starts',
                     @enabled=1,
                     @freq_type=64,
                     @freq_interval=0,
                     @freq_subday_type=0,
                     @freq_subday_interval=0,
                     @freq_relative_interval=0,
                     @freq_recurrence_factor=0,
                     @active_start_date=20100929,
                     @active_end_date=99991231,
                     @active_start_time=0,
                     @active_end_time=235959
       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
END
ELSE
       PRINT 'JOB ALREADY EXISTS ON THIS SERVER'
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
       IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO




Comments

Most Popular

DMV To List Foreign Keys With No Index

Easy to use WMI commands to query windows system

Generate SQL script to extract user permissions from a SQL database