Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TCP Provider: Timeout Expired (258) errors

Posted on 2008-10-01
8
Medium Priority
?
2,513 Views
Last Modified: 2013-12-05
This is driving me crazy.

Here is the scenario.  I have recently converted the publisher from SQL 2000 to SQL 2005 SP2 Workgroup Edition.  We have 10 Subscription databases that are still on SQL 2000 SP3.  We have completely rebuilt 2 of the subscription servers within the past 3 weeks and they are on SQL2005

The problem is occuring on both SQL Server 2005 Subscription Servers. At first, I was able to replicate to the SQL 2005 subscription databases, but now, I am experiencing Timeouts in many different places.

The problem was first reported by Access 2003 users when they receiving a non-descript "Timeout Expired" message box. I am intermittently unable to connect to the server from a remote location.  I get a more detailed Timeout Expired message. Occassionally (about 20% of the time), I get the message when attempting to open a table containing 10 records from within SQL Server Management Studion. And, lastly, I am unable to perform the merge replication.  After 30 minutes or so, I get the Timeout Expired Messages shown in the images below.

On both of the servers, we were using Symmantec Endpoint Protection, so we removed it from one of the PCs to no avail.  Now, when we attempt to sync to that server, the pc interface locks up.  When I stop the replication from the server side, it frees up the interface (mouse and keyboard).  Wierd.

Occassionally, I will get the Timeout Expired message when replicating to the SQL 2000 subscription database, but after a few attempts, it is able to complete successfully.

Some random notes:
I have Shared Memory (1),  TCP/IP(2), and Named Pipes(3) enabled.

The problem is at the server level, and not the replicated database level because I get similar timeout errors with attempting to work with a non-replicated database.

In the Server Properties/Connections, I Enable Remote Connections and have set a query timeout of 600 (default was 0)

I have tried to Boost SQL Server priority, but that did not work.

Any help would be greatly appreciated.
Thanks, Russell
TimeoutExpired.doc
0
Comment
Question by:infinitisys23
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 4

Expert Comment

by:Makolyte
ID: 22613759
So timeout errors are occuring after the servers were rebuilt, and they occur no matter what you're trying to do on SQL. So it's not specific to replication. And you have the remote connection stuff enabled. My instinct is that this is a network issue, because you said you rebuilt the servers...so many double check the network settings.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 22614498
while there can't be ready solution, here are the hints about what to look at:

  1. is there timeout if you run the query locally on the server?
  2. on server run profiler while you run the query from remote that times out. You will see if the delay is in incoming query, in processing, or in sending the output.
  3. selectively disable named pipes and tcp, see if it's the same with every protocol.
  4. if it's network issue (which is most likely), run network monitor and see what it says.
0
 

Author Comment

by:infinitisys23
ID: 22615041
The problems seemed to start when I applied SP2 to both servers. Are there any known issues?  I am going to uninstall SQL Server and recreate the subscription without applying any service packs at this time.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 4

Expert Comment

by:Makolyte
ID: 22615144
From this article: http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/ReadmeSQL2005SP2.htm

It says: "In some cases, the new initialization process can cause a time-out error to occur. For example, complex Data Flow tasks can contain many components that use connections such as multiple sources, destinations, and lookups."

To correct this problem, simply increase the value of the Timeout property for the failing connections. Performance of the package after initialization is not affected. "

I dont think this applies to your case, but I just wanted to point out that they documented a timeout problem.
0
 

Author Comment

by:infinitisys23
ID: 22623807
The reinstallation of SQL Server with no Service Packs at the subscriber did NOT fix the problem.  Upon further research, I found a couple of obscure articles that provided a solution.

The cause is a known bug in SQL Server where it gets into an infinite loop when trying to run sp_MSmakegeneration. An update was supposedly due for release in July/August 2008 to resolve this problem - in the meantime a recommended solution is to set generation_leveling_threshold to 0 using the following SQL Code.
"update sysmergepublications set [generation_leveling_threshold] = 0"

To quote one of the articles I read . . . .
"update sysmergepublications set [generation_leveling_threshold] = 0" is actually configurable via sp_addmergepublication parameter @generation_leveling_threshold.  This basically tells the merge agent how many commands to put into a given generation.  This setting coupled with DownloadGenerationsPerBatch will tell the merge agent how much to download at a given time.  For example, if DownloadGenerationsPerBatch is set to 1, and generation_leveling_threshold is set to 100, and there are 10 generations to process (totaling 1000 changes), merge agent will sync 1 generation at a time, and if it fails anywhere along the line, it will restart from the failed generation.  This is highly useful in scenarios with unreliable network as it gives the agent a chance to make progress amongst the many retries.  By setting it to 0, it will try to sync 1000 changes all at once, and if by the 999th change the network cuts out, merge agent will retry from the beginning all over again."

From what I understand, this problem may reappear in the future until we are able to apply SQL Server Cumulative Update 8 (Build 3257) as well as a soon-to-be-released Hotfix.  In the meantime, if it occurs again, we can run the command
"update sysmergepublications set [generation_leveling_threshold] = 0"
and then after successful sync set the value back to its default by running
"update sysmergepublications set [generation_leveling_threshold] = 1000"

Here are some articles on the subject if you are a glutton for punishment:

http://iainmagee.wordpress.com/2008/05/09/sql-replication-fun/
http://forums.microsoft.com/msdn/showpost.aspx?postid=1655111&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=3 
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 22624184
this explains the issues with subscription; but how they explain this, and especially the bold text:

"I am intermittently unable to connect to theserver from a remote location.  I get a more detailed Timeout Expiredmessage. Occasionally (about 20% of the time), I get the message whenattempting to open a table containing 10 records from within SQL ServerManagement Studio. "


0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 22981524
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question