On-Start SQL

August 17, 2009

 On-Start SQL Server 2005

  • Auto starting a user stored procedure at SQL Server startup: sp_procoption: Sets stored procedure for autoexecution.
  • A stored procedure that is set to autoexecution runs every time an instance of SQL Server is started

sp_procoption ‘sp_QActivityLockedObjects’,’startup’,’true’

sp_procoption ‘ usp_OnSQLStart’,’startup’,’true’

  • To disable the SP from the SQL Server Startup

 sp_procoption ‘sp_QActivityLockedObjects’,’startup’,’false’

  •  To Check the SPs that run when SQL Server Starts, run the following query

 select name from master.dbo.sysobjects where type = ‘P’ and objectproperty(id, ‘ExecIsStartup’) = 1