Connection pools being reset with Error: 18056, Severity: 20, State: 46. & Perfmon Counters not showing

Question

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.

Answer 1

_{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.

Answer 2

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.)