Jan
15
2013

[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed

Found SQL Server 2005 SP4 CU3 A/P failover cluster SQL services are stopping and starting several times in interval of 30mins to 1 hour. Following errors are found,

 

Application Log Errors:

[sqsrvres] printODBCError: sqlstate = 08S01; native error = 0; message = [Microsoft][SQL Native Client]Communication link failure

[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed

[sqsrvres] OnlineThread: QP is not online.

[sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746; message = [Microsoft][SQL Native Client]Communication link failure

[sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746; message = [Microsoft][SQL Native Client]TCP Provider: An existing connection was forcibly closed by the remote host.

[sqsrvres] printODBCError: sqlstate = 08S01; native error = 40; message = [Microsoft][SQL Native Client]Communication link failure

[sqsrvres] printODBCError: sqlstate = 08S01; native error = 40; message = [Microsoft][SQL Native Client]TCP Provider: The specified network name is no longer available

 

 

 

I did some preliminary investigation on this issue, mostly ERROR 19019 occurs when there is huge I/O activity happening, SAN drives are outdated or not performing well, or there are lot of writes happening which is causing heave I/O.

 

Below are some suggestions by DBA’s on various forum tried to fix the issue, these are not recommended in every environment as issue might occur due to some change activity on application end in last few days, below will come handy if you are unable to locate a fix, below workaround may help you fix issue quickly,

 

Suggestions/Solution provided on various forums,

 

1. Increase time-out value for the SQL Server Resource in the Cluster Administrator from current value to double

 

2. Disable SynAttackProtect.

a)
Launch regedit.exe

b)
Add DWORD value named SynAttackProtect under registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\

c)
Set data value to 0

http://blogs.msdn.com/sql_protocols/archive/2006/04/12/574608.aspx

 

3. Disable to TCP Chimney Offload feature.

a)
Launch regedit.exe

b)
Edit DWORD EnableTCPChimney under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters, set data value to 0

c)
Edit DWORD EnableRSS under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters, set data value to 0

d)
Edit DWORD EnableTCPA under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters, set data value to 0

e)
Restart the server

http://support.microsoft.com/kb/942861

 

4. Set TcpMaxDataRetransmissions to 30 (decimal)

 

5. Set KeepAliveInterval to 25000 (decimal)

                Go to SQL Server Configuration Manager > SQL Native Client Configuration > Client Protocols > TCp/IP > right click Properties > Update Keep Alive Interval from 1000 to 25000

                ~Do same for 32 bit section as well

 

IsAlive troubleshooting: http://blogs.msdn.com/b/joaol/archive/2009/04/20/sql-server-resource-isalive-troubleshooting.aspx

 

Must read: http://blogs.msdn.com/b/mssqlisv/archive/2008/05/27/sql-server-intermittent-connectivity-issue.aspx

 

Useful References:

http://dba.stackexchange.com/questions/1214/help-solving-a-clustering-issue-sqsrvres-odbc-sqldriverconnect-failed

http://www.sqlservercentral.com/Forums/Topic399630-149-1.aspx

http://paulduffett.wordpress.com/2008/09/18/dude-wheres-my-cluster-service/#comments

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 103 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

November 2018
M T W T F S S
« Nov    
 1234
567891011
12131415161718
19202122232425
2627282930  

View Post by Categories

%d bloggers like this: