Replication Error
Creation date: 8/21/2022 10:31 PM
Updated: 10/21/2025 5:45 AM
1. When data in reporting site and live site is not synchronized, replication not working could be the root cause.
2. So if it is confirmed that
replication between production server and reporting server is down, the first step to solve this issue is to reboot the reporting server.
3. There is no issue with the data. Normally we are pointing you all to the reporting database as we do not want user's frequent queries slowing down the production transaction server.
4. Then the data from production server will be synchronized with the reporting server.
Useful Commands
===============
Under Distribution DB
SELECT TOP 100 * FROM
[distribution].[dbo].[MSdistribution_history] WITH(NOLOCK)
ORDER BY time DESC
Check commands to be replicated
SELECT TOP 100 * FROM
[dbo].[MSrepl_transactions] WITH(NOLOCK)
WHERE entry_time BETWEEN '2024-04-30 14:40:00' AND '2024-04-30 14:45:00'
ORDER BY entry_time DESC
Under Subscription DB (need to stop the agent first)
To Update The Time if Required
--UPDATE MSreplication_subscriptions
--SET transaction_timestamp = CAST(0x001004DB0005A9090014 AS binary(15)) + CAST(SUBSTRING(transaction_timestamp, 16, 1) AS binary(1))
----WHERE UPPER(publisher) = UPPER(N'EMM01SMES\EMM01SMES')
----AND publisher_db = 'mTrac_Live'
----AND (publication = 'pub_mTrac_Live'
----OR (publication = N''
----AND independent_agent = 0
----AND UPPER('pub_mTrac_Live') = N'ALL'))
// Command to check the tables with records to be replicated. the higher the number is, the slower it is
USE distribution;
SELECT
t.article_id
,a.[article]
,COUNT(*) AS PendingCommands
FROM dbo.MSrepl_commands AS t
JOIN dbo.MSsubscriptions AS s
ON t.article_id = s.article_id
AND t.publisher_database_id = s.publisher_database_id
JOIN dbo.[MSarticles] AS a
ON t.[article_id] = a.[article_id]
--AND t.[publication_id] = a.[publication_id]
-- optionally join distribution history table to know up to what seqno has been delivered
GROUP BY
t.article_id
,a.[article]
DataImage55.jpg