Number of open connections SQL server
Hi all,
Normally, when you’re coding on a website or a system you’re probably using connection to an SQL server database.
When you are the coder or you are a consultant coming to a company to check the code or the server or anything else for that matter, you sometimes need to check the number of connections that are open right now.
This can be done to check the quality of the code regarding connections. If the code does not close the connections, eventually the server will close the pool and the website will no longer work.
I have seen people solving that by upping the number of allowed open connections. That of course is not the solution (not the best solution).
OK, so if you are like me and you are running 1-9 servers that are dedicated to you, each running dozens or hundreds of DB’s, this is an absolutely great method to catch a glimpse on whats going on with your connections.
Run this code on your server and see what happens:
<p>
</p>
<pre style="border-style: none; margin: 0em; padding: 0px; overflow: visible; text-align: left; line-height: 12pt; background-color: #f4f4f4; width: 100%; font-family: 'Courier New',courier,monospace; direction: ltr; color: black; font-size: 8pt;"><span id="lnum2" style="color: #606060;"> 2:</span> <span style="color: #0000ff;">WHERE</span> dbid > 0</pre>
<p>
</p>
<pre style="border-style: none; margin: 0em; padding: 0px; overflow: visible; text-align: left; line-height: 12pt; background-color: white; width: 100%; font-family: 'Courier New',courier,monospace; direction: ltr; color: black; font-size: 8pt;"><span id="lnum3" style="color: #606060;"> 3:</span> <span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span> dbid</pre>
<p>
</p>
This code will display a list of all the databases on your server with the open connections on each of them.
For security reasons I can’t show the entire result set, but here’s a sneak peak:
** This was checked on SQL server 2005/2008 both express and full versions.
Good luck!