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
SQL Server – How to troubleshoot query timeouts - SQL Server DBA
Troubleshoot memory pressure with Default Trace and Server Memory Change event class
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |