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 send you an email alert whenever SQL Server instance is started/restarted. Make sure to update the following lines in the script with your email address:
IF (@@servername like '%dev%' or @@servername like '%tst%')
SET @CriticalOperator = '<youremailaddresshere>'
ELSE
SET @CriticalOperator = '<yourpageraddresshere'
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
This obviously would not alert you when the SQL Server goes down, which is what you would really want to monitor. However, I still have found this useful when SQL Server gets restarted or fails over because of some technical glitch in the network, OS, etc., or when someone, intentionally or not, restarts the SQL Server at unscheduled and inconvenient times. Since the SQL Server comes right back up, such events may not get caught by monitoring tools. These events can surely have negative consequences as they lead to connection resets, transaction rollbacks, or worse.
-- 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