Solved

TCP Provider: Timeout Expired (258) errors

Posted on 2008-10-01
8
2,347 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 4

Expert Comment

by:Makolyte
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now