Solved

TCP Provider: Timeout Expired (258) errors

Posted on 2008-10-01
8
2,425 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

738 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