Run code automatically when SQL Server starts

Run code automatically when SQL Server starts

By John Sheesley, Special to ZDNet Asia
Thursday, December 11, 2008 11:55 AM

Here is an overview of the ways to execute SQL stored procedures or SQL Agent jobs on startup.

The ability to run procedures or SQL Agent jobs on startup provides a great tool for database administration.

Running procedures on startup gives the ability to run cleanup routines, maintenance, auditing, business rules, or run a SQL trace.

The SQL Server service
The SQL Server service (MSSQLSERVER) is the Windows service that runs the SQL Server database engine. This service provides the ability to execute stored procedures when the service is first started.

You can specify this startup procedure by using the sp_procoption system stored procedure.

In the following script, I use the code from a previous article where I outlined how to define your own custom trace scripts.

use master

GO
CREATE PROCEDURE usp_StartTrace
AS
BEGIN
DECLARE @TraceID INT
DECLARE @MaxSize BIGINT

SET @MaxSize = 15

EXECUTE sp_trace_create @TraceID output,
@options = 2,  --//rollover the file when max size is reached
@tracefile = N'C:Tracefile.trc', --//trace file name
@maxfilesize  = @MaxSize --//maximum file size

SELECT @TraceID  --//output the traceid create for viewing

DECLARE @On BIT
SET @On = 1
EXECUTE sp_trace_setevent @TraceID, 10, 15, @On
EXECUTE sp_trace_setevent @TraceID, 10, 16, @On
EXECUTE sp_trace_setevent @TraceID, 10, 1, @On
EXECUTE sp_trace_setevent @TraceID, 10, 9, @On
EXECUTE sp_trace_setevent @TraceID, 10, 17, @On
EXECUTE sp_trace_setevent @TraceID, 10, 6, @On
EXECUTE sp_trace_setevent @TraceID, 10, 10, @On
EXECUTE sp_trace_setevent @TraceID, 10, 14, @On
EXECUTE sp_trace_setevent @TraceID, 10, 18, @On
EXECUTE sp_trace_setevent @TraceID, 10, 11, @On
EXECUTE sp_trace_setevent @TraceID, 10, 12, @On
EXECUTE sp_trace_setevent @TraceID, 10, 13, @On

EXECUTE sp_trace_setevent @TraceID, 12, 15, @On
EXECUTE sp_trace_setevent @TraceID, 12, 16, @On
EXECUTE sp_trace_setevent @TraceID, 12, 1, @On
EXECUTE sp_trace_setevent @TraceID, 12, 9, @On
EXECUTE sp_trace_setevent @TraceID, 12, 17, @On
EXECUTE sp_trace_setevent @TraceID, 12, 6, @On
EXECUTE sp_trace_setevent @TraceID, 12, 10, @On
EXECUTE sp_trace_setevent @TraceID, 12, 14, @On
EXECUTE sp_trace_setevent @TraceID, 12, 18, @On
EXECUTE sp_trace_setevent @TraceID, 12, 11, @On
EXECUTE sp_trace_setevent @TraceID, 12, 12, @On
EXECUTE sp_trace_setevent @TraceID, 12, 13, @On

EXECUTE sp_trace_setstatus @TraceID, 1

END

Go

I can then call the sp_procoption system stored procedure to execute the procedure defined above when the SQL Server service starts.

EXECUTE sp_procoption
@procname = 'usp_StartTrace',
@optionname = 'startup',
@optionvalue = 'on'

Conclusion
The ability to execute stored procedures at service startup is a powerful one. You can use this functionality to ensure Sarbanes Oxley compliance for SQL Server logins, ensuring all logins are written to a file, or to ensure that system cleanup routines are ran when the system starts.