SQL Server – Configure Maximum Concurrent Connections Without SSMS

sql serversql-server-2012

I'm having an issue in a production environment and I was trying to simulate it in my local SQL Server Express instance.

I used SSMS to change the "maximum number of concurrent connections" from "0" (max = 32767) to "2".

After restarting the service, I'm able to connect to it with SSMS but when I try to open Server Properties (or "New Query" window) to change the setting back to "0", SSMS crashes.

Is there any other place I can change this setting without SSMS (e.g. registry)?

Best Answer

This should do it:

EXEC sp_configure 'user connections', 0;
RECONFIGURE

That will set the number of connections back to the default, which is 32767.

Once you have SSMS open, disconnect from the server in the "Object Explorer" Window. Then you should be able to create a new query window and connect to it there. Alternately, you could use sqlcmd.exe to connect from the command-line without having SSMS open, then run the command from there.

The setting is stored in the master database, and can be seen through the following statement: 

SELECT *
FROM sys.configurations c
WHERE c.name = 'user connections';

sys.configurations is actually a view, defined as:

CREATE VIEW sys.configurations AS
    SELECT configuration_id,
        name,
        value,
        minimum,
        maximum,
        value_in_use,
        description,
        is_dynamic,
        is_advanced
    FROM sys.configurations$
    WHERE is_not_use = 0

The only way to see the sys.configurations$ table is through the Dedicated Admin Connection, more commonly known as the DAC. The DAC needs to be turned on through SQL Server Configuration Manager, since it is off by default in SQL Server Express.

Related Question