Start Up Procedures for SQL Sever (2005)

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'