SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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
EXECmsdb.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' ANDcategory_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
GOTOEndSave
QuitWithRollback:
       IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO




Mission: SQL Homeostasis

Drupal is a Senior SQL Server consultant with over 20 years of experience in engineering innovative SQL Server solutions for high growth organizations. A truly SQL Server specialist and IT generalist, Drupal is a certified MCDBA, Oracle OCP-DBA and IBM WebSphere Administrator. He is also ITIL V3 certified at the Foundation and Intermediate levels as well as PMP.

Comments

Leave a comment on the original post [sqlpal.blogspot.com, opens in a new window]

Loading comments...