SQL Server Event Handling: Startup

The startup of an instance of SQL Server is a rare event. It only happens once for the life of the database engine process (sqlservr.exe). The timing of the event makes it appealing, though. It's an ideal opportunity to run T-SQL initialization tasks or other T-SQL tasks that check various environment settings. By taking advantage of the startup event, a DBA is often able to find problems before the problems find her.

Handling The Startup Event

The system stored procedure sp_procoption can be used to set a stored procedure for automatic execution, which causes the procedure to run every time the instance of SQL Server is started. (sp_procoption can also be used to turn off a procedure for automatic execution.) Below is an example that sends an email alert.

USE master
GO

CREATE PROCEDURE dbo.StartupNotification
AS 
    DECLARE @Subj NVARCHAR(255) 
    DECLARE @MailBody NVARCHAR(MAX)
    DECLARE @Restart NVARCHAR(MAX)

    SET @Subj = @@SERVERNAME + ' - SQL Server service restart'
    SELECT @Restart = CONVERT(NVARCHAR, sqlserver_start_time, 109) 
    FROM sys.dm_os_sys_info

    SELECT @MailBody = 'The SQL Server service for the following was (re)started.' + 
        CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
        'SQL Server: ' + @@SERVERNAME + CHAR(13) + CHAR(10) +
        'Last Restart: ' + @Restart

    EXEC msdb.dbo.sp_send_dbmail
        @recipients = 'Dave@Celtics.com', 
        @Subject = @Subj,
        @body = @MailBody
GO

EXEC sp_procoption @ProcName = 'dbo.StartupNotification',
    @OptionName = 'startup',
    @OptionValue = 'true' 
GO

If you have more than one task you want to run at startup, you could include code for each task in a single stored procedure. But that's generally bad coding practice. Go with the modular approach and create a separate stored procedure for each distinct task. With sp_procoption, you can set more than one stored procedure for automatic execution. The MSDN documentation was not clear regarding the order of execution, though. I thought there might be something similar to sp_settriggerorder, but I wasn't able to find anything like that. I ran a test on SQL 2014 with 3 stored procedures set for automatic execution.

USE master
GO

CREATE PROC dbo.Task01
AS
RAISERROR( 'Task 01', 16, 1) WITH LOG
GO

CREATE PROC dbo.Task02
AS
RAISERROR( 'Task 02', 16, 1) WITH LOG
GO

CREATE PROC dbo.Task03
AS
RAISERROR( 'Task 03', 16, 1) WITH LOG
GO

EXEC sp_procoption @ProcName = 'dbo.Task03',
    @OptionName = 'startup',
    @OptionValue = 'true' 
GO

EXEC sp_procoption @ProcName = 'dbo.Task01',
    @OptionName = 'startup',
    @OptionValue = 'true' 
GO

EXEC sp_procoption @ProcName = 'dbo.Task02',
    @OptionName = 'startup',
    @OptionValue = 'true' 
GO

Task03 got set for automatic execution first, followed by Task01 and Task02. After restarting SQL and reading the error log, I found the execution order was Task01, Task02, Task03. That's not the same order I was expecting. The results seem to be consistent, though. After two more restarts, I got the same execution order each time. YMMV.

If the order matters, my suggestion would be to create exactly one stored procedure for automatic execution. Then within that procedure, execute the stored procedures for your other tasks that you want run at startup. This allows you to specify the order. Here's an example.

USE master
GO

CREATE PROCEDURE dbo.StartupTasks
AS 
    --Specify stored proc order (as needed).
    EXEC startup.TaskZ;
    EXEC startup.TaskY;
    EXEC startup.TaskX;

    EXEC startup.Task3;
    EXEC startup.Task1;
    EXEC startup.Task2;
GO

EXEC sp_procoption @ProcName = 'dbo.StartupTasks',
    @OptionName = 'startup',
    @OptionValue = 'true' 
GO

Other Notes

In order for a stored procedure to be set for automatic execution, it must reside in the [master] database (this is noted in the documentation). It also has to be in the [dbo] schema. However, I didn't see that detail in the documentation. Trying a schema other than [dbo] resulted in this error:

Msg 15398, Level 11, State 1, Procedure sp_procoption, Line 73
Only objects in the master database owned by dbo can have the startup setting changed.

Startup Event Ideas

There are lots of possibilities for the tasks you might want to perform at startup. Below are some of the tasks I've used in the past, plus some other ideas I've considered.

Send An Email Alert
Getting an email when the SQL Server instance starts can help identify unplanned outages. If there was a restart of the SQL Server service or reboot of the host server, wouldn't it be nice to know?

Check Service Logins
Make sure there haven't been any changes to the Windows Login(s) used to run SQL Server and/or the SQL Agent, which could have some unwanted repercussions.

Check Available Disk Drive Space
Pretty self-explanatory. Bad things happen when you run out of disk space.

Enable Global Trace Flags
There's at least one other way to accomplish this. It's probably much more common, if not more popular. However, enabling global trace flags via T-SQL has its advantages. When saved in a script and added to source control, it allows the DBA to keep a history (with comments!) of what trace flags are (or were) enabled.

Check Computer Name And Domain Name
If there were changes to either of these properties since the previous startup, this could be the sign of a cloned virtual machine, or simply a change you weren't made aware of ahead of time.

Check Host Memory and CPU
If there was a hardware change you didn't know about, startup would be a good time to find out about it.