?
Solved

TCP Provider: Timeout Expired (258) errors

Posted on 2008-10-01
8
Medium Priority
?
2,472 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

800 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