remote SQL query reset ("The semaphore timeout period has expired. [SQLSTATE 08S01] (Error 121) Communication link failure [SQLSTATE 08S01] (Error 121).")

Dear SQL expertise,

We keep experiencing this error message an the SQL query dropped:

"The semaphore timeout period has expired. [SQLSTATE 08S01] (Error 121)  Communication link failure [SQLSTATE 08S01] (Error 121)."

Any one of your has experience on this and how to solve this?

This link : http://support.microsoft.com/kb/325487 seems not helpful.

DBA100.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
marrowyungSenior Technical architecture (Data)Author Commented:
lorddef,

Have you tried this:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/3b5bbee9-4200-46b4-b668-6d0ef82299b1

You did experinece this before, right?

DBA100.

0
 
lorddefCommented:
That kb answer suggests the problem is network based rather than sql server based, but the symptoms could also be exaggerated by heavy io load.

Make sure you're up to date with service packs, firmware and drivers etc, and if the problem persists try to see if disabling Priority Boost helps.

If not try:

1. Set TcpMaxDataRetransmissions to 30 (decimal);
http://technet2.microsoft.com/WindowsServer/en/library/7dac9001-3e55-4e9c-b0fa-52841ece2fdd1033.mspx

2. Set KeepAliveInterval to 25000 (decimal).
http://technet2.microsoft.com/WindowsServer/en/library/734570a2-06d6-450e-b765-ccfa7530af491033.mspx 




0
 
lorddefCommented:
Yeah, haven't seen the affinity mask options before, but 1 & 2 above resolved the issue here.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
marrowyungSenior Technical architecture (Data)Author Commented:
lorddef,

But in the internet , there are 2 x error message similiar but not the same, mine is about the SQL server, not server/netwrok.

DBA100.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
This can be the other version:"

[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.
0
 
lorddefCommented:
Well you didn't post the full error message initially, however this still doesn't rule out a network issue as the cause of the problem.  I guess need to look at any other variables in your setup to figure out if it's a problem with sql server instance, or if the root cause is network related ; any connection pools? max connection limit? soes it happen under heavy load (network/io/cpu) or even when there is no load. Are there any firewalls between the client and server that could be closing the connection etc...

0
 
marrowyungSenior Technical architecture (Data)Author Commented:
lorddef,

I did, let me requote, the message shown by our college even worse.

"The semaphore timeout period has expired. [SQLSTATE 08S01] (Error 121)  Communication link failure [SQLSTATE 08S01] (Error 121)."

This is the one, when you search from internet, it has differnet version.

I tested it copy coping many GB of files and it doesn't create problems at all, that;s why. Some articles even say change registry of the network!!

Do you think it is rare to change the default network confiugration, what if later on we have network problkme again and what to change then??

DBA100
0
 
lorddefCommented:
You clearly posted a set of 3 differing error messages later after the initial post of 2.

I think you need to describe the symptoms and circumstances a little more clearly.  You seem to be adamant that the problem is not network based when the error messages seem to suggest this is a possibility, why so?   The problem could be due to a timeout or sql server issue, but copying large files isn't really a good test of network stability.

How often does the error occour, and under what circumstances?
Does it still occour if the client is run locally on the server rather than across the network?
Is the server io or cpu bound at the time of the issues?
Is there any deadlock, long running queries or excessive locks on the server at the time?
Is the client in question executing a long running query?
Does a running constant ping against the target server from the client that's having problems list any dropped packets, if so what percentage?
If there is an outage does it only happen when your client is running?
What does the server profiler show?
What does the windows event log show?
0
 
lorddefCommented:
Also the link you posted above indicates an error within sql server that results in a thread getting stuck on a cpu that is saturated is the cause of that particular issue. Looking at cpu states, the profiler and sql server details in perfmon should allow you to see if this could be the case.

You could try the options suggested by the poster in that thread, or try disabling priority boost as that has had issues previously.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
you know, I just copy and paste the whole error message and search it, bascially we will find only 2 groups of related answer.

one is SQL related and one is network and server related.

We did found that this is not occur regularily. we try to transfer file transfer inbound and outbound, if this is not a good test then what is a good test for network stabliity?

We did test on Windows log but nothing shows out. This is the only message.

DBA100.
0
 
lorddefCommented:
I see there may be a language barrier here, but you still haven't clearly outlined the problem.

You won't get any closer to the soloution unless you investigate into the areas I posted above, and it's going to be hard for anyone else to see what the problem might be unless you can do so and relay the information back to us.

Sorry but without this information there's nothing more I can suggest.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
lorddef,

By making it simple, I get this message, how to solve it?

"The semaphore timeout period has expired. [SQLSTATE 08S01] (Error 121)  Communication link failure [SQLSTATE 08S01] (Error 121)."

DBA100.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this support note should help for that error:
http://support.microsoft.com/kb/325487
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
angelIII,

This one is already known, this doen'st work,

We already know that this is not netwrok problem, my college send this to me also.

Some case even say this only appear on cluster nodes but not all nodes, how can we trust this?

I strongly belive that the server, after it is configured, it has no problme on network, please note that this error not happening all the time, right now we dont' have this any more.

The only thing we did is unclick the "boost SQL server proprity" and this can help on this can right now the system might not only listen to SQL server mostly, but also other request like Network request.

DBA100.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.