SQL Server Performance

run a store procedure after SQL server reboot???

Discussion in 'General DBA Questions' started by ufobox, Jan 3, 2003.

  1. ufobox New Member

    I want to run a store procedure after SQL server reboot.

    Currently, I create a sql job call it and set job-schedule as 'Start automatically when SQL Server Agent Starts' to achieve it.

    Does anybody know other ways?

    Thanks
  2. bradmcgehee New Member

    SQL Server has the ability, if properly configured, to look for stored procedures to run automatically when the mssqlserver service starts. This can be handy if you want a particular action to occur on startup, such as the loading of a specific stored procedure into cache so that it is already there when users begin accessing the server.

    By default, the "scan for startup procs" is set to "0", which means that a scan for stored procedures is not done at startup. If you don't have any startup stored procedures, then this is the obvious setting. There is no point spending resources looking for stored procedures that don't exist.

    But if you do have one or more stored procedures you want to execute on server startup, then this option has to be set to "1", which turns on the startup scan.

    Use "sp_procoption" to specify which SPs should be executed at startup.




    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. satya Moderator

    Using SP_PROCOPTION with 'scan for startup procs' is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change scan for startup procs only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.

    HTH



    Satya SKJ

Share This Page