Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are using SQL authentication & .net 4.0 Connection strings to connect to an Enterprise Edition 2012 SP1 SQL Server on a windows 2008r2 Enterprise Server. 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 and 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.

Errorlog 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 from CSS blog

http://blogs.msdn.com/b/psssql/archive/2010/08/03/how-it-works-error-18056-the-client-was-unable-to-reuse-a-session-with-spid-which-had-been-reset-for-connection-pooling.aspx

and this one by the scholar Aaron Bertrand (I know the error number is different but the failure ID is the same with a number of the messages are identical)

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

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 ?

share|improve this question
2  
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
2  
@auto_close, Heresy!! :) ..No its always up, this is on a 24x7 website with heavy usage –  DamagedGoods Sep 5 '13 at 13:44
1  
I've been facing a similar issue on SQL SERVER 2008r2(120 db of 2gb) on Win 2008R2 erratically. After some digging , it's seems to have been caused by the "AutoClose" setting which was turn on and affecting the connection pool. Check it and i hope it makes a differences –  Raymond Jan 8 at 19:19
2  
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 upto 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, soon as I found the job, slapped the person who abused their rights on that server round the head, stopped the job, all error messages for connection pools stopped. –  DamagedGoods Jan 9 at 14:11
show 11 more comments

1 Answer

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 http://blogs.msdn.com/b/psssql/archive/2013/02/13/breaking-down-18065.aspx 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.)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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