We are using SQL Authentication (to reduce the number of connection pools) & .NET 4.0 connection strings to connect to SQL Server Enterprise Edition 2012 SP1 on a Windows 2008 R2 Enterprise Server:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

We use about 50 servers split into 8 different groups different parts of a website.

Our website is using this SQL Server to log visit tracking data. Over the last few days it has spat out the following messages about the resetting connection pools:

The client was unable to reuse a session with SPID 1327, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

Error log reads:

Error: 18056, Severity: 20, State: 46.
The client was unable to reuse a session with SPID 959, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Login failed for user 'xxxx'. Reason: Failed to open the database 'xxxxxxxx' configured in the login object while revalidating the login on the connection. [CLIENT: 10.xx.xx.xxx]

After some digging, I found this document on the CSS blog: How It Works: Error 18056 – The client was unable to reuse a session with SPID ##, which had been reset for connection pooling and this one by Aaron Bertrand: Troubleshooting Error 18456. I know the error number is different but the failure ID is the same with a number of the messages are identical).

Failure ID 46 suggests that the login did not have permissions. Our logins default to the master database and the db name is specificied in the connection string.

I wanted to check the number of connection strings pools, etc. and checked all the counters in Perfmon for .Net Data Provider for SqlServer. It only gave me the option of defaultdomain9675 for the instance, so I selected that assuming that is a system generated ID name for our Datacentre network. Unfortunately all of the counters are reading zero. On one of our other main servers the connection pools are hovering around 10 which is what I expected to see on a healthy server with that kind of load.

My question is 3 fold

  1. Can anyone suggest why the Windows 2008 R2 Server is not showing .Net Data Provider for SqlServer?

  2. Has anyone experienced this as I obviously believe that the login not having permissions is a red herring ?

  3. If different groups of web servers have the same connection string syntax but with slightly different whitespace, would this cause the server to use another connection pool?

Min & max memory settings are 20GB & 58GB respectively. The server is a dedicated database server with 64GB of RAM. I don't think memory is the issue as the box seems to have a decent page lfe expectancy. Auto close is not enabled. The server is always up: this is a 24x7 website with heavy usage.

  • 3
    We have this same issue on our servers (.NET application/Windows 2008 R2/SQL Server 2008 R2/SQL login) intermittently; I've never been able to track down why this happens... basically we've given up trying at this point. We had this issue on .NET 3.5 before upgrading to 4.0, too. I would love to hear if anyone has solved this! – Jon Seigel May 20 '13 at 14:16
  • 1
    @jonSeigel Hi John, I have managed to determine that the server in question is actually using ocnnection pooling properly using the following document on extended events. sqlserverpedia.com/blog/sql-server-bloggers/… im currently trying to adapt the Xevents to find the necessary information to give me a total for the number of connection pools – DamagedGoods May 23 '13 at 10:21
  • Is the server in question using mirroring? I've seen this error message on the primary machine when databases are failed-over to the secondary. – Hannah Vernon Oct 1 '13 at 2:51

1 - can't say for sure, I'd have to go find a server to dig into myself.

2 - yes, I see this periodically in my environment though we're not on sql 2012 yet on the systems we see this from. You may also want to check this post though State 46 seems to be related to having a specific Database=xxx in the connection string, does that db still exist?

The way my network is set up I suspect it's the network's automatic closing of tcp sessions after being 5 minutes idle that's the issue - neither the db nor the client is closing the session so the connection pool still thinks the connection is open and tries to use it only to find it's not really open anymore. You don't mention how the network between your web servers and db is configured, maybe your case is similar.

Another possibility may be the (old, not sure if ever really resolved, see http://support.microsoft.com/kb/942861) issue about TCP Chimney Offload settings.

3 - My understanding is pooling requires exact string matches, so whitespace and different order of parameters would cause different pools. (If I'm wrong on that, please let me know.)


Community Wiki answer originally left as a comment by the question author

In my case it turned out to be a runaway logging table that somebody had switched into verbose to troubleshoot an issue, but forgot to switch off. It ended up logging up to 1000 records a second.

Another job was attempting to delete old records from the table. ended up getting itself into knots as was locking up when trying to delete, blocking all those inserts that it ran out of connection pool resources.

As soon as I found the job, slapped the person who abused their rights on that server round the head, and stopped the job, all error messages for connection pools stopped.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.