Data Management

How do I run code automatically when SQL Server starts?

The need to run a stored procedure or SQL Agent Job when SQL Server or SQL Agent starts is a common requirement in the world of SQL Server administration. Today, consultant Tim Chapman gives 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. Try out this extremely useful functionality.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

3 comments
tony
tony

Thanks for writing this up! The ability to run a custom script / stored procedure at SQL service startup can be very useful. I'm intrigued by the tracing stored procedure as well, and it would be helpful if there was a hyperlink back to the article where you discuss that - I must have missed that article somehow. Thanks again, Tony

BALTHOR
BALTHOR

Show me how this code works from beginning to implementation.

Editor's Picks