I was recently asked where to go in Configuration Manager to change the settings for xp_cmdshell. It was then that I realized that the existence of sp_configure is probably not universal knowledge. I was truly shocked. For some reason I had thought that DBA’s were born with this knowledge. As this turns out not to be the case, I felt it would be a good idea to give sp_configure a brief run through.

Quick disclaimer: This stored procedure modifies system settings. Be sure you understand the setting you are changing and the need for the change before making it.

The system stored procedure sp_configure is used to view or modify a number of different system settings. These settings include the default MAXDOP for the server, whether SMOs is enabled, whether xp_cmdshell is enabled and a long list of others. To get a complete list you can either query sys.configurations or run sp_configure with no parameters.

At this point you might have a couple of questions:

• Why is the list from sp_configure shorter than the list from sys.configurations?
• If I can enable xp_cmdshell using sp_configure, why isn’t that setting showing up?

The answer to both of these is the same. Some configurations are considered Advanced. If you want to see them you will need to change the setting show advanced options (using of course sp_configure).

EXEC sp_configure 'show advanced options', 1 
RECONFIGURE

“But wait,” you’re wondering “Why are you are adding in this RECONFIGURE command?”

Because sp_configure only changes the configured value. RECONFIGURE updates the running value with the configured value (there are a few exceptions where a reboot or instance restart is required). So in order to actually change the currently running configuration value you have to use both commands. Fortunately RECONFIGURE checks that the configured value is valid before setting it as the running value. If you are absolutely certain you know what you are doing and need to update one of the configuration values with something outside of its allowed range you can include the WITH OVERRIDE option of the RECONFIGURE command.

So here’s the thing, if you are going to be administrating SQL Server you need to have at least a passing familiarity of the sp_configure and RECONFIGURE commands and a reasonable idea of what settings they modify.

Filed under: Microsoft SQL Server, Settings, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, instance settings, language sql, microsoft sql server, system functions, T-SQL