Wednesday, December 30, 2015

How to limit concurrent connections to the instance temporarly

This is based on a question I was asked today on concurrent connections. For running some procedures and making sure that no one can access the server while they are running but few, how to instruct SQL Server to allow only n number of connection?

Simply, if you want to limit your instance to limited connection, it can be configured with a property in Connection section;

By default, it is 0, means unlimited but limited to connections allowed based on version and edition. If you want to make sure that only 3 concurrent connections can be made, set this as 3 and restart the service. Same can achieve using sp_configure stored procedure too.

EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'user connections', 3 ;

Note that even we execute reconfigure, it needs a restart for setting the value as running value.

Once this is set, when you try to make this 3rd connection, you will get this messege;

No comments: