Oct 262011
 

That’s simple, setup a job and run the procedure and schedule it runs when SQL Server agent started. See the problem here? My title is “when SQL Server Start”, not “When SQL Server Agent”. SQL Server can start without agent. Procedure in SQL Server can be started automatically when SQL Server starts. In order to do that, you have to set “scan for startup procs” through sp_configure function, and then use sys.sp_procoption to set a procedure to be launched as server started. See example below.

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'scan for startup procs', 1
reconfigure with override
exec sp_configure 'scan for startup procs'
go
alter procedure simpletest
as
begin
 waitfor delay '01:00:00'
 return
end
go
exec sys.sp_procoption 'simpletest','startup', 1

Once a procedure marked as start up automatically, it will be launched by server as it starts in a user session (session id may be less than 50). You are able to kill that session while it’s running. The login name is sa. When the procedure is started while SQL Server Starting, it will also be logged

2009-09-15 00:50:09.01 spid7s Launched startup procedure 'simpletest'.

There are some restrictions for creating auto-start procedure:

  • You have to be a sysadmin
  • The owner of procedure must be dbo
  • The procedure must be in master database
  • Procedure must not have a parameter

There are two ways you can determine if a procedure can be started automatically or not.

  • ObjectProperty(object_id,’ExecIsStartup’) = 1
  • select * from sys.procedures where is_auto_executed = 1

  2 Responses to “Setup Procedure to Run Automatically When SQL Server Starts”

  1. Just a word of caution here, watch out for parallel procedure launches that will eat threads. Its recomended (until die-hard) to call multiple procs from within one startup proc. Dont shoot them all for startup.

    • Good point. I can’t agree more. It’s definitely not something that should be used frequently. Reasonable amout of startup procedures might be acceptable.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |