Identify out of synch subscriptions

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

SELECT


'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 + '


if @publisher_cnt<>@subscriber_cnt


print ''' + a.publisher_db + ' : ' + a.destination_object + ' not synch.''' + '


go

'
FROM distribution..MSarticles a


INNER JOIN distribution..MSsubscriptions s


ON a.publication_id=s.publication_id


WHERE s.subscriber_db<>'virtual'


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:

http://msdn.microsoft.com/en-us/library/ms162843.aspx
http://www.replicationanswers.com/TableDiff2005.asp

Conclusion

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.