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, or more accurately SQL Server Agent service. It assumes that SQL Sever Agent service is started/restarted along with the database service, which we know is not always true.
That being said, the script basically creates a SQL Server Agent job that gets executed whenever SQL Server Agent service is started.
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 - Sole purpose of this script is to send email notification whenever SQL Server Agent service is started.
- It does this by executing SQL Agent job that sends email notification when the job completes, irrespective of job out.
- The scripts create an email operator if it doesn't already exist Make sure to update the value for @SQL_Startup_Operator_Email to the email address of the notification recipients
- Script then create a SQL Server Agent job that gets executed at SQL Server service startup */ USE [msdb] GO -- 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
-- Set here the email address of operators who would receive the notification DECLARE @SQL_Startup_Operator_Email NVARCHAR(500) SET @SQL_Startup_Operator_Email = '<youremailaddresshere>'
-- SQL Agent operation with this name will be created if doesn't already exist DECLARE @Operator_Name NVARCHAR(500) SET @Operator_Name = 'DBA - Startup Operator'
-- SQL Agent job will be created wit this name if doesn't already exist DECLARE @Job_Name NVARCHAR(500) SET @Job_Name = 'DBA - SQL Server Service Start Notification'
-- CLEAN UP -- DROP EXISTING JOB AND EMAIL OPERATOR IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @Job_Name) EXEC msdb.dbo.sp_delete_job @job_name=@Job_Name, @delete_unused_schedule=1; IF EXISTS (select * from dbo.sysoperators where name = @Operator_Name) EXEC msdb.dbo.sp_delete_operator @name=@Operator_Name; -- Create the operator if it doesn't already exist USE [msdb] IF NOT EXISTS (select * from dbo.sysoperators where name = @Operator_Name) EXEC msdb.dbo.sp_add_operator @name=@Operator_Name, @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=@SQL_Startup_Operator_Email, @category_name=N'[Uncategorized]' 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 VARBINARY(16) IF not EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @Job_Name) BEGIN RAISERROR('Creating SQL Agent job "%s" ....', 10, 1, @Job_Name) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@Job_Name, @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=@Operator_Name, @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_name=@Job_Name, @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=@Job_Name, @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 DECLARE @jobId_String NVARCHAR(100) SELECT @jobId_String = CONVERT(NVARCHAR(100), @jobId, 1) RAISERROR('Job ID: %s', 10, 1, @jobId_String) END ELSE PRINT 'JOB ALREADY EXISTS ON THIS SERVER' COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO
This obviously would not alert you when the SQL Server goes down and stays 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.
Alternatively, you could use sp_procoption, to execute a stored procedure at SQL Server startup, to send the email notification.
-- set a procedure for automatic execution USE master GO EXEC sp_procoption 'sp_sqlserver_startup_notifier', 'startup', 'true' -- Verify SELECT [name] FROM sysobjects WHERE type = 'P' AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1;
However, this should not be seen as a substitute for monitoring with appropriate tools.