Start up procedures execute when the SQL Server Service is started. This can be advantageous at times. First you have to set the “Scan for Startup Procs” via sp_configure. Another, according to BOL is sp_procoption this sets the “Scan for startup Procs” and which one to execute.’ Below is the code for the proc that I want to run DBA_StartUpProc when the SQL Server service is started. After the Procedure code below there is code to run sp_procoption.
The Purpose of DBA_StartUpProc is to notify me when a SQL Server Service starts, if it’s one of our production servers I have it text me and other Operators. Why? There are several reasons: We have a cluster and I want to know when it fails over – a failover basically restart SQL Server on the other node. If a server goes bump in the night and restarts I want to know so I can check the services and jobs running on that server. To verify that something did happen that was supposed to like HW/SW/OS updates that cause a reboot or restart of SQL Server
The main flow of this proc is to determine if it’s a production server, then get then get the email addresses from the operators table in msdb, then it’s just formatting the addresses and setting parameters to call msdb.dbo.sp_send_dbmail
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DBA_StartUpProc]
AS /*
12/11/2007 Dave Ott
Purpose of this proc is to notify when service is started
production servers will send an email and a text message,
all others will get an email
*/
SET NOCOUNT ON
DECLARE @ProductionServerList VARCHAR(100),
@IsProductionServer BIT,
@recipients VARCHAR(4000),
@email VARCHAR(1000),
@Subject VARCHAR(100),
@Message VARCHAR(100)
DECLARE @emails TABLE ( Email VARCHAR(1000) )
SET @ProductionServerList = 'Server1,Server2,Server3'-- so far
SET @IsProductionServer = 0
-- set is production server flag
IF CHARINDEX(@@SERVERNAME, @ProductionServerList) <> 0
SET @IsProductionServer = 1
-- get list of emails and in the case of production text/Pager emails
INSERT INTO @emails
SELECT CASE WHEN email_address IS NULL THEN ''
ELSE email_address
END
+ -- if production get pager adress too
CASE WHEN @IsProductionServer = 1
AND pager_address IS NOT NULL
THEN CASE WHEN email_address IS NULL THEN pager_address
ELSE ';' + pager_address
END
ELSE ''
END
FROM msdb.dbo.sysoperators WITH ( NOLOCK )
WHERE email_address IS NOT NULL
-- get all Recipants
SET @recipients = ''
WHILE EXISTS ( SELECT TOP 1
*
FROM @emails )
BEGIN
SET @email = ( SELECT MIN(Email)
FROM @emails
)
SET @recipients = @recipients + -- we need a ; between emails
CASE WHEN LEN(@recipients) = 0 THEN @email
ELSE ';' + @email
END
DELETE FROM @emails
WHERE Email = @Email
END
-- send email
SET @Subject = 'SQL Server ' + @@servername + ' was started...'
SET @Message = 'Which means that it was stopped for some reason'
EXEC msdb.dbo.sp_send_dbmail @recipients = @recipients,
@profile_name = 'Profile', @body = @Message, @subject = @Subject,
@importance = 'High'
GO
-- Accpording to books online this also sets 'scan for startup procs' in sp_configure
EXEC sp_procoption N'[dbo].[DBA_StartUpProc]', 'startup', '1'
