SQL Diagnostic Manager automatically limits the number of connections that a collection service may make to an individual monitored server. This serves to protect the monitored server from having hundreds of connections opened by the monitoring tool right when things are starting to go wrong.
Usually the connection count from SQL Diagnostic Manager should hover around 5, plus one for each actively connected desktop client that is doing real time monitoring of a server. When SQLdm detects that it has 30 open connections to a server – idle or active – it will stop all monitoring and begin returning the following error:
The SQLdm Collection Service has too many outstanding connections to SERVERNAME. The current connection count is 31. This may be a sign of a problem with the monitored server, with one of the SQLdm collectors, or with a user defined counter. To prevent exacerbating this problem collection is being halted until the connection count falls to 30 or fewer.
There are a few scenarios where this may happen:
- There are a large number of desktop clients connecting to and monitoring a single server
- A problem on the monitored server is causing connections to hang, and thereby stack up
- This is particularly a problem with certain external commands, such as xp_fixeddrives
- A SQL Diagnostic Manager custom counter is not behaving properly and is running long enough to cause connections to pile up
- There is a bug in SQL Diagnostic Manager
Now, that very first case can sometimes be frustrating. If your whole team is looking at a server at the same time, that’s the last moment when you want your monitoring to go down. Fortunately, this is a configurable setting!
In the SQLdm Collection Service config file (SQLdmCollectionService.exe.Config in the collection service install location) you’ll find a section that looks like this:
<Services> <CollectionService instanceName="Default" servicePort="5167" managementServiceAddress="VHARPL2" managementServicePort="5166" heartbeatIntervalSeconds="180" /> </Services>
To change the number of permitted connections, simply add a new entry for maxConcurrentServiceConnections, as follows:
<Services> <CollectionService instanceName="Default" servicePort="5167" managementServiceAddress="VHARPL2" managementServicePort="5166" maxConcurrentServiceConnections="50" heartbeatIntervalSeconds="180" /> </Services>
After making your change, restart the Collection Service to save the change.
The normal use case would be to increase this value, but you can also decrease it. Decreasing it too far will keep monitoring from working even in very normal situations, however, so use caution!