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
