One of the main tasks of DBA is to monitor replication status. There may be situations where subscriptions may out of synch due to various reasons. If this happened, DEV and QA will face issues and they will start communicate with DBAs to verify the replication status. If you’ve not setup proper monitoring mechanism to see replication staus this is the usual story.
However if you have already setup monitoring mechanism the DBAs will identify the issues upfront without making panic on DEVs and QAs.
This blog is on how to identify out of synch subscriptions especially in transactional replication.
Using a dynamic SQL script
I have created a simple script which is mentioned below to identify out of synch subscriptions.
SET NOCOUNT ON
'declare @publisher_cnt bigint
declare @subscriber_cnt bigint
set @publisher_cnt=0; set @subscriber_cnt=0
select @publisher_cnt=count(1) from ' + a.publisher_db + '..' + a.article +
';select @subscriber_cnt=count(1) from ' + s.subscriber_db + '..' + a.destination_object + '
print ''' + a.publisher_db + ' : ' + a.destination_object + ' not synch.''' + '
FROM distribution..MSarticles a
INNER JOIN distribution..MSsubscriptions s
This is a dynamic sql script and copy and paste the result of the query and execute it against the required server which you want to see the out of synch.
The dynamic sql script should be executed in a server which is distribution database is available.
Using tablediff utility
Tablediff is another utility which comes with SQL Server. By using this tool you will not only identify the out of synch subscriptions but it will generate the SQL statements to synch those subscriptions.
However this takes more time on very large databases and also I have experienced failures for some subscriptions.
This utility is residing in the following folders;
SQL Server 2008 - C:\Program Files\Microsoft SQL Server\100\COM
SQL Server 2005 - C:\Program Files\Microsoft SQL Server\90\COM
For more details:
There may be other solutions available for the same purpose. I have used these two methods frequently in our servers. I have a batch file for each database to use the tablediff so that it is easy for me to execute it at any given time.
The SQLOS Prior to SQL Server 2005, SQL Server runs as a user mode process like any other Windows application and it depends on...
I wanted build an expression to make a folder name dynamically. (Basically expressions are anyway dynamic in nature) So I simply used the ...
OSTRESS is a Microsoft tool comes with RML utilities package and it uses to stress SQL Server. This is especially useful when you want to tr...
When querying sys.dm_exec_requests dmv, you can see the waiting requests and the resource those requests are waiting for. Sample of such val...