http://www.sqlservercentral.com/blogs/andy_warren/2008/08/20/troubleshooting-high-connections-amp-what-s-the-application-name/

Printed 2014/10/31 09:38AM

Troubleshooting High Connections & What's The Application Name?

By Andy Warren, 2008/08/20

I was visiting with a client recently when they asked me to come take a look at an incident in progress, the number of connections to the server had just about doubled over what they were normally. As far as troubleshooting this scenario I always start by checking for blocking. If you've got spids blocked they can't go back into the connection pool, forcing the connection pool manager to open up new threads.

No blocking? The next step is to run two different group bys against sysprocesses, one by hostname, the other by program name. I'm looking for a clue that will show all the connections are being generated from one machine or one application, or both! In this case we could see that the connections were evenly distributed across the web farm, but trying to break down by application wouldn't work because they didn't set the application name in their connection string. Fixing this is easy, just add the following to the current connection string:

;Application Name=My App v1.1.1

We couldn't identify anything obviously wrong on the server, in the error/event log, or in their application logging. Within a few minutes we saw a nice reduction in connections and soon things were back to normal. Would having the application name have made a difference? Unknown to unlikely in this case, but given that it's so easy to do, why not make the change so that next time you have one more piece of data?


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.