Mar
12
2012

Troubleshooting transactional replication timeout error in SQL Server 2000

Timeout error is mostly encountered in merge or transactional replication scenarios where data needs to be synced at regular time intervals, now sometimes due to network issue or bad data replication start failing with timeout errors, now to troubleshoot them follow below steps,

Source: http://www.mssqlcity.com/Articles/Replic/troubleshooting_replication.htm and http://support.microsoft.com/kb/312292

First, when you encounter timeout error, go to distribution agent SQL job (in case of push subscription, agent job will be present on distributor server and in case of pull subscription, agent job will be present on subscriber server), check the job agent history and enable ‘Show Step Details’ and check if any error is logged, if logged, check and troubleshoot

Secondly, enable output verbose setting to collect log of replication in a text file, steps to do same is as follows,

How to enable output logging for a replication agent
You can use the following steps with Microsoft SQL Server 2000 Enterprise Manager,

······1.In SQL Enterprise Manager, click the Replication Monitor Node to open the Replication Monitor on the server configured as the Distributor.
······2.Click Open on the Publishers node.
······3.Click the publisher that has the publication that has the problem.
······4.Click Publication.
······5.In the right-hand pane of SQL Enterprise Manager is a list of the agents related to the publication. You see the Snapshot Agent, Log Reader Agent and the Push/Pull Subscription Agent.
······6.Identify the agent for which you need to set up output logging.
······7.Right-click the replication agent you identified in step 6, and then click Agent Properties.
······8.Click the Steps tab, and then edit the Run Agent step.
······9.At the end of the string under command, add:
······
······-Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel [0|1|2]
······
······Specify either 0, 1, or 2 after the -Outputverboselevel parameter. If the verbose level is 0, SQL Server only prints the error messages. If the verbose level is 1, SQL Server prints all the progress report messages. If the verbose level is 2 (default), SQL Server prints all the error ···messages and progress report messages, which is useful for debugging.
·········
······10.Click OK to save the changes, and then close the Edit Job Step dialog box.
······11.Click OK to save the changes, and then close the Replication Agent Properties dialog box. If the agent is set to run continuously, stop and restart the replication agent so that SQL Server logs the messages to the log file specified in step 9. If the file already exists, the agent appends the output to the file.

Now once output is collected, you can go through it and locate issue and fix the issue.


Basic Checklist to ensure replication works correctly,

1. Check the hardware requirements.

2. Check the software requirements.

3. Check that you use the latest SQL Server service pack

4. Check that account the MSSQLServer and SQLServerAgent services runs under belongs to the Administrators local group and is a member of the Domain Users group.

5. Check that you have sysadmin permissions on the SQL Server.

6. Check that ‘trunc. log on chkpt’ option is turned off, if you want to set up Transactional replication.

**7. Check the Agent history to determine which task failed and the reason for failure.
—-
To view the Agent history, you can do the following:

a. Run SQL Server Enterprise Manager.
b. Expand a server group; then expand a server.
c. Expand Replication Monitor and choose the Agent to view history.
d. Right-click appropriate publication and select Agent History…

**8. Choose appropriate Agent profile for your replication model.
—-
For example, if the replication will work through the slow link, choose Slow link agent profile. To choose the Agent profile, you can do the following:

a. Run SQL Server Enterprise Manager.
b. Expand a server group; then expand a server.
c. Expand Replication Monitor and choose the Agent.
d. Right-click appropriate publication and select Agent Profiles…

Note. You can also create your own Agent profile (click the New Profile button under the Agent profile window).
—-

9. Set the rowcount or rowcount and checksum validation to avoid the problems with data consistency.

**10. Increase the QueryTimeout value in the Merge Agent or Distribution Agent profile, if the Merge Agent or Distribution Agent fails on timeout.
—-
The QueryTimeout value in the Merge Agent or Distribution Agent profile indicate the number of seconds before the queries issued by the agent times out.
To increase the QueryTimeout value in the Merge Agent or Distribution Agent profile, you can do the following:

1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Replication Monitor and choose the Agent.
4. Right-click appropriate publication and select Agent Profiles…
5. Click the New Profile button to create the new profile with the appropriate QueryTimeout value.
6. Choose the newly created profile.

—-

11. If you receive an “Access Denied” error when start Snapshot Agent, make sure the account that SQL Server Agent runs under have default access and launch DCOM permissions.

12. Make sure that the snapshot folder is shared correctly.

13. If you get “Couldn’t deliver schema information.” error when synchronize the Internet publications, set up FTP server at the Distributor and set the Merge Agent command line to include an FTP address.

14. If conflict occurs when merging newly inserted rows that contain identity columns, you must assign each Subscriber that will insert new rows containing an identity a unique range of identity values.

15. You cannot specify the uniqueidentifier column with the ROWGUIDCOL property as the primary key of the published table when you use merge publishing from SQL Server to Jet 4.0.

16. Use ALTER TABLE statement instead of using Design Table in SQL Server 7.0 Enterprise Manager to modify a table that has the NOT FOR REPLICATION property.

17. Use Replication Conflict Viewer to get more information about conflict details.


A v. good article on troubleshooting other common replication issues and share some scripts as well to fix known issues:
http://www.sqlmag.com/article/sql-server/troubleshooting-transactional-replication


Other Useful Links:
http://searchsqlserver.techtarget.com/tip/SQL-Server-database-replication-tutorial
http://searchsqlserver.techtarget.com/tip/Simplify-SQL-Server-replication
http://searchsqlserver.techtarget.com/tip/Monitor-database-mirroring-and-replication-after-a-SQL-Server-upgrade

Download PDF

Related Posts

About the Author: Nitin Garg

Indian born, trekker, biker, photographer, lover of monsoons... I love to blog the topics I research and find useful for self or online community to save time and energy :) Everything you read on my blog is my own personal opinion!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 174 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

May 2019
M T W T F S S
« Nov    
 12345
6789101112
13141516171819
20212223242526
2728293031  

View Post by Categories

%d bloggers like this: