The sys.configurations view can be used to get SQL Server configuration information. Let’s analyze the output of sys.configurations view.
As shown in above snapshot, the sys.configuration view lists down different server configuration options such as user connections, fill factor(default),nested triggers etc. with their value, minimum and maximum limit and description. It also tells whether a configuration option is dynamic or advanced options via is_dynamic and is_advanced columns.
These options can be recorded at regular intervals for a SQL Server for sake of inventory or to help troubleshoot issues.
The sp_configure stored procedure is used to modify values for these configuration options. Suppose a requirement is to limit number of user connections to a SQL Server to 50. The user connections configuration can be modified as shown below.
The configuration options user connections is an advanced option, so first step is to set show advanced option to 1 and then set user connections to 50. Once done, query the sys.configurations view to verify the changes.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
