{"id":131155,"date":"2023-04-24T06:54:00","date_gmt":"2023-04-24T06:54:00","guid":{"rendered":"https:\/\/www.stellarinfo.com\/blog\/?p=131155"},"modified":"2023-09-05T08:25:52","modified_gmt":"2023-09-05T08:25:52","slug":"how-to-troubleshoot-sql-server-transactional-replication-issues","status":"publish","type":"post","link":"https:\/\/www.stellarinfo.com\/blog\/how-to-troubleshoot-sql-server-transactional-replication-issues\/","title":{"rendered":"How to Troubleshoot SQL Server Transactional Replication Issues?"},"content":{"rendered":"

The transaction replication is a common technique that can be applied between two SQL Servers to replicate the data of two databases. In this, you need to create a snapshot with the replicated database objects (tables, views, etc.) and the changes are replicated later. Sometimes, issues arise during the transaction replication process. Let’s discuss some common issues and errors related to transaction replication and the solutions to fix them.<\/p>

Common Transaction Replication Errors and Issues<\/h2>

Here, we will discuss a list of errors and issues related to transaction replication and how to fix them.<\/strong><\/p>

Issue 1: <\/strong>SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name.<\/h3>

Solution:<\/strong> This error can occur in both the publisher and the subscriber. When someone changes the computer name, the value is not reflected in SQL Server. So, you need to update the information.<\/p>

To check the server name, use these sentences:<\/strong><\/p>

\n

SELECT @@SERVERNAME<\/code><\/p>\n\n\n\n

Compare the server’s name with the values of the sys.servers view.<\/code><\/p>\n\n\n\n

SELECT * FROM sys.servers<\/code><\/p>\n<\/div>

If the values do not match, drop the current server’s name using the sp_dropserver stored procedure.<\/p>

sp_dropserver 'old_server_name';<\/code><\/p>

GO<\/code><\/p>

Also, add the new server’s name using the sp_addserver stored procedure.<\/p>

sp_addserver 'new_server_name', local;<\/code><\/p>

GO<\/code><\/p>

Finally, use the SQL Server Configuration Manager to restart the SQL Server Services.<\/p>

\"Restarting<\/figure>

Issue 2: <\/strong>The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity.<\/h3>

Solution:<\/strong> You need to verify that the records are being replicated to the destination and connections to the Subscriber, Publisher, and Distributor are still active.<\/p>

If you have several publishers and subscribers, the activity could be high and the replication agent may not respond. You can use the Task Manager to verify the activity and other running processes.<\/p>

\"CPU<\/figure>

If the CPU, disk, or memory usage is high, check which processes are consuming more resources.<\/p>

\"Apps<\/figure>

Coordinate with the team and check which processes can be closed.<\/p>

You can also use the Performance Monitor to check different counters of the CPU, memory, and minimize the replication or SQL Server activity.<\/p>

\"SQL<\/figure>

You can add different counters and also monitor them.<\/p>

\"Counters<\/figure>

Optionally, you have Data Collector Set to schedule, monitor, and record the counters’ activity.<\/p>

\"Data<\/figure>

If the problem is inside SQL Server, you can use the sp_who2 to check all the processes running inside SQL Server.<\/p>

sp_who2<\/code><\/p>

Check the processes that are taking more CPUTime and DiskIO, and coordinate with the users to check which processes need to be stopped.<\/p>

\"Results<\/figure>

Also, try to minimize replication usage. Make sure that only the necessary information is being replicated and irrelevant information is excluded.<\/p>

Issue 3:<\/strong> Error 20598<\/h3>

The common error messages are:<\/strong><\/p>