0

I have read:

but am no closer to understanding or resolving my issue which is as follows. I have posted here instead of at dba.stackexchange.com for no other reason than I believe this will receive a bigger readership.

This is also not your typical make sure you close your connections with a using clause as no matter whether the stored proc is run from SQL Agent, SQL Server Management Studio or via an external application which is not an ASP.NET application I see this behaviour.

My environment is as follows:

  • SQL Server 2012 - 11.0.2100.60 (X64) Standard
  • Windows Server 2008 SP2, IIS 7

The SQL Server and IIS are all running on the same machine.

I have the following intermittent problem where a particular stored procedure runs that is resource intensive (definitely CPU 100%, I/O is hard to measure) and occasionally causes a web forms application to stop responding to requests.

The webforms application is running in its own application pool (with default settings so 100 data connections max) and the way to recover things is to restart the application pool which cleans up the connection pool. Very occasionally if you wait long enough (say 5+ minutes) the connection count drops and things recover, but this is not consistent.

To isolate this stored procedure from the webforms app I changed the connection string for the stored procedure so that it had its own application name as I thought (from my reading) that it would keep it from interfering with the webforms application but the behaviour persists which is that I see the following in the event log:

A number of these:

Exception information: 
    Exception type: EntityException 
    Exception message: The underlying provider failed on Open.

ultimately followed (some time later) by:

Exception information: 
    Exception type: InvalidOperationException 
    Exception message: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

at which point the website has stopped serving up requests. Looking at what is being reported by SQL Server via:

SELECT COUNT(*) FROM sys.dm_exec_sessions

I see a larger number than usual of connections.

So assuming I have no opportunity to change the stored procedure, can anyone tell me what is happening here because clearly I have some kind of gap in my understanding. And if possible how I go about fixing this issue?

TheEdge
  • 9,291
  • 15
  • 67
  • 135
  • Can you share the connection string you are using (with sensitive info redacted)? – Steve Ford Nov 01 '17 at 13:50
  • Does the stored procedure use transactions or begin/try blocks etc. – Steve Ford Nov 01 '17 at 16:33
  • @SteveFord Yes it does use transactions as well as begin/try blocks. In fact the SP contains calls to 10 or so other SP's which in turn call their own SP's. The top level SP has 3 separate transactions and then some of the inner SP's might have a single transaction. At most there is only ever 1 outer transaction (in the top level SP) and say 2 inner transactions. – TheEdge Nov 01 '17 at 20:28
  • Connection String: Provider=SQLNCLI11.1;Integrated Security="";Persist Security Info=False;User ID=%DbUser%;Initial Catalog=%DatabaseName%;Data Source=%DatabaseServer%;Initial File Name="";Server SPN="";Application Name=%AppName%; The % tokens are replaced prior to using. This is the connection string when called from the external app. When called from SQL Server Agent the string is obviously a little different, but the same behaviour is seen. – TheEdge Nov 01 '17 at 20:32
  • I'm wondering whether you are facing a command timeout, if an uncommitted transaction is left the connection won't be returned to the pool. See this SO answer https://stackoverflow.com/questions/1539564/what-happens-to-an-uncommitted-transaction-when-the-connection-is-closed/1539686#1539686. If you can it might be worth increasing the command timeout to > than the expected SP duration. – Steve Ford Nov 03 '17 at 14:56
  • @SteveFord Changing the command timeout is not really an option as the SP runs for 5mins. I will however investigate the SO post. In there I should be able to at least work out if I have a transaction that is left hanging. Unless you know off the top of your head? – TheEdge Nov 03 '17 at 23:50
  • If you are able to execute a SET XACT_ABORT ON before you call your stored procedures then this may prevent a transaction holding on to your session. See this Dan Guzman blog http://weblogs.sqlteam.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-Stored-Procedures.aspx . – Steve Ford Nov 06 '17 at 13:58

0 Answers0