Does this issue go away past SQL Server 2008 R2?

Question

Does this issue go away past SQL Server 2008 R2?

dajonx

SSCertifiable

Points: 6950
More actions
April 4, 2016 at 7:23 am

#326244

Hi,
I'm on SQL Server 2008 R2 and have been seeing this error message pop up a lot:
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occured while attempting to connect to the Principle server. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
....
....
Error Number:-2,State:0,Class:11
I haven't found a solution to this so I was wondering if it's been fixed since 2008 R2.
Thank you!
My Blog!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

I've only seen that error come up in the event of timeouts, or, when we had an app that was using an older version of OLEDB to connect to the database. It wasn't a problem with SQL Server.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

dajonx SSCertifiable Points: 6950 More actions · Answer 2

Oh really?? Can you please tell me more about the older version of OLEDB and how you fixed it?? :w00t:

My Blog!

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Oh, we're going off memory here. I did run into this issue on 2008r2, but only with a single app. We tracked down which app was having the failure and then investigated the server. It was running an older version of OLEDB. We updated it, the problem went away.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 4

Check out the following link maybe this will help you out: http://blog.sqlauthority.com/2016/01/26/sql-server-timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation-or-the-server-is-not-responding/[/url]

dajonx SSCertifiable Points: 6950 More actions · Answer 5

I checked the driver version and it's running:

SQL Server 2008 0x730B0003

SQL Server 2000 0x71000001

Is there a SQL Server 2008 R2 driver? Could I install a SQL Server 2012 or 2014 driver?

My Blog!

dajonx SSCertifiable Points: 6950 More actions · Answer 6

So I attempted the install:

"Microsoft® SQL Server® 2008 R2 Native Client

Microsoft SQL Server 2008 R2 Native Client (SQL Server Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2000, 2005, or 2008. SQL Server Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2008 R2 features. This redistributable installer for SQL Server Native Client installs the client components needed during run time to take advantage of new SQL Server 2008 R2 features, and optionally installs the header files needed to develop an application that uses the SQL Server Native Client API.

Audience(s): Customer, Partner, Developer

X86 Package (sqlncli.msi)

X64 Package (sqlncli.msi)

IA64 Package (sqlncli.msi)"

But the installer said:

"Installation of SQL Server 2008 R2 Native Client failed because a higher version already exists on the machine. To proceed, uninstall the higher version and then run SQL Server 2008 R2 Native Client Setup again."

So I'm guessing the OLEDB isn't the cause? It's still odd that some of the connections are using SQL Server 2000 OLEDB... Any possible insight on that?

My Blog!

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 7

My experience with this is about 6 or 7 years in the past. I remember getting the issue and how we solved it. That's about it. Sorry.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eric M Russell SSC Guru Points: 125561 More actions · Answer 8

By default, a SQL Server processes will not timeout, unless it is in deadlocked state (an unresolvable locking contention) with another process. In a worst case scenario, a poorly constucted SQL SELECT statement on a large database will continue table scanning, hashing, and waiting for days and weeks. Timeouts, specifically the type that raise this 0x80004005 error, are enforced at multiple points within the client and middle tier stack. Below is a good article that summarizes where to look.

http://vyaskn.tripod.com/watch_your_timeouts.htm

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

dajonx SSCertifiable Points: 6950 More actions · Answer 9

The error message doesn't look like it's related to a query timeout since it appears that it's trying to connect to the Principal server. The database is configured for database mirroring and when I look at the SQL logs on the mirror, there are users who are attempting to connect for some odd reason. And then there are some connections that are using SQL 2000 drivers on the principal.

My Blog!