Sp_configure in SQL Server 2005

Today I was reading few of my old articles, when I seen sp_configure there, idea of this article pops up in my mind as there are many properties to set and I have used few of them only in my past article. There are many more one can use in real life so I have started digging into Microsoft Book Online for those properties of sp_configure and here is a results of that.

Sp_configure: When you run this SP in your query window, it will show you current setting of the server. You can execute it like below

EXEC sp_configure

NOTE: many of the server property change will not take effect until and unless your restart server or its service that is why we have to use RECONFIGURE command rather than restart.

“Show Advanced Option”: This property will show you all advanced option you can set with sp_configure. If you run only EXEC sp_configure command as per above, you may see few rows as a result, I got 14 rows in my development server but there are lot more option other than these few rows. To see all the advanced option, run following commands in your query window.

–set advanced option to true

EXEC sp_configure ‘show advanced option’,1

–reconfigure the server so that property change

–take effect right a way



–list the properies after advanced option true

EXEC sp_configure


“Query Wait”: When memory is not available to execute the query, you can specify seconds, how many seconds will it wait to execute before gets time out?

EXEC sp_configure ‘query wait’,30



Min and Max server memory: These properties mainly used to change the memory usage of SQL Server. I have used these properties along with “awe enabled” property in my following article.


“Min memory per query”: default 1MB set as a “Min memory per query” but you can increase or decrease this limit for better performance but beware, increasing limit could cause memory shortage problem as well.

EXEC sp_configure ‘min memory per query’,1024



“Scan for startup procs”: I have used this property in my article of Startup Stored Procedure at http://www.sqlhub.com/2009/03/startup-stored-procedure-sql-server.html

This property is mandatory if you are making startup stored procedure and want SQL Server to scan that SP while SQL Server restart.

EXEC sp_configure ‘scan for startup procs’,1



Reference: Ritesh Shah

Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of


Startup Stored Procedure -SQL-Server 2005

Startup Stored Procedure -SQL-Server 2005
All of you may aware with Stored Procedure but you may not aware with startup stored procedures which
suppose to run every time SQL-Server’s services restart.
This task may be useful for some administrative task, i.e.: you may know that TempDB will be recreated from scratch whenever server restarts. You may want some user to grant permission of TempDB every time it is being created.
Before you start generating startup stored procedure, have a look at some basic ground regarding that.
You have to make sure that ‘Scan for Startup Proc’ option is set to 1. You can see that option and make it disable and enable with following queries.
–this will show you all advance option
EXEC sp_configure ‘show advanced option’, ‘1’;
–this will enable startup procedure
EXEC sp_configure ‘scan for startup procs’, ‘1’;
Now, you can create stored procedure and it will be set as startup proc, before you create it, you have to follow some strict guideline.
–Procedure should be reside in MASTER database
–Its owner should be DBO
–Mark your stored procedure as startup stored procedure with sp_procoption
–Only sysAdmin can set sp_procoption
–Your procedure shouldn’t have any input parameter or return any output parameter

Now, we will create one stored procedure and will mark it as startup procedure.
–Creating strored procedure
CREATE PROC spCreateDatabaseAtStartup
EXEC(‘CREATE database StartupDatabase’)

–Marking SP as startup
exec sp_procoption N‘spCreateDatabaseAtStartup’, ‘startup’, ‘on’
In sp_procoption first parameter is our stored procedure name. Second parameter is “OptionName” but fortunately or unfortunately there is only one option name “startup” J and third parameter is “OptionValue” that should be “on” or “off”.
Reference: Ritesh Shah