asked
Intermittent connection failures
Hello everyone, I am experiencing problems with 2 SQL Server 2008R2 with intermittent connection failures. The two situations have different software accessing DBs on SQL instance but they do have many things in common... I'd love to be pointed into the right direction as I understand the matter is quite complicated, I'm not asking for the solution (if someone has it, will be welcome tho :) ). OK, configuration: Virtual machine with Win Server 2008R2 and a lot of RAM (20GB on one and 12GB on the other one). SQL Server 2008R2 installed in VM, VM is powered by VMWare ESXi. Box 1: Lots of queries from remote machines using SQL Native client (CPU constantly running 20-50%), lotsa inserts also. Some other less demanding applications on other boxes using OleDB connections. OleDB apps stop connecting to SQL and get timeout expired. Box 2: Lots of queries from remote machines using SQL Native client (CPU constantly running 20-50%), lotsa inserts also. Other demanding apps using OleDB. This time OleDB apps always work but SQL Native client apps do get errors. IMPORTANT: In all cases DBs used by applications are different. Client software is also VERY different in Box 1 from Box 2. As you can see the configuration is very similar: ESXi VMs with lots of RAM (20GB/12GB) and same OS (Windows 2008R2) SQL Native Client AND OleDB used together High demanding apps Random timeouts in connecting from other boxes Of course nothing has been found in SQL Server logs. TIA to everyone reading Paolo
The issues you get with timeouts is that once they've errored, there's generally no evidence of them from the DB side of things. The DB was probably happily chugging away at something, denying resources to other client requests. What you may have to do (and I don't know whether it is possible for you) is recreate the scenario, or at least try and capture what is going on, on the server at the time of one of these timeouts. Using something as simple as `sp_who2` or more indepth like [Adam Machanic's *Who Is Active* script][1]. Then you will have an indication of what is going on at the DB level, possibly indicating some long term blocking. It **may** be that you find nothing, meaning some other issue further up the stack. [1]:
http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx
9 People are following this question.
