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