Solved

TCP Provider: Timeout Expired (258) errors

Posted on 2008-10-01
8
2,403 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

829 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