scan for startup procs Option – when to use

28 August,2013 by Jack Vamvas

 Scan for Startup Procs Option is a SQL Server database option.  Why use it ?

From SQL Server BOL “Use the scan for startup procs option to scan for automatic execution of stored procedures at Microsoft SQL Server startup time.”

Why use it?  Some examples of applying Scan for Startup Procs Option

1)     Create a stored procedure mark it for start up. The stored procedure creates temporary objects in the TEMPDB . These temporary objects are available in the TEMPDB before any requests are made

2)     I’ve seen DBAs trigger a stored procedure on the SQL Service service startup that checks the current configuration. It’s a basic audit process.   I prefer to run a SQL Server daily health check

3)     Create a stored procedure to turn traces on capture deadlocks.

Example: DBCC TRACEON (1222,-1)

To check the current setting for scan for start up procs use this query:

 

USE MASTER 
GO 
SELECT VALUE, VALUE_IN_USE, DESCRIPTION 
FROM SYS.CONFIGURATIONS 
WHERE NAME = 'scan for startup procs'

 

To determine if any procedures are set to start automatically:

 

select * from sys.procedures where is_auto_executed = 1

 Read More

SQL Server – How to troubleshoot query timeouts - SQL Server DBA

Troubleshoot memory pressure with Default Trace and Server Memory Change event class

SQL Server – DBA tactics


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on scan for startup procs Option – when to use


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer