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
Can anyone suggest why the Windows 2008 R2 Server is not showing
.Net Data Provider for SqlServer?
Has anyone experienced this as I obviously believe that the login not having permissions is a red herring ?
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.