Run code automatically when SQL Server starts
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.
Play video
There are currently no comments for this post.