SP_SHOWPENDINGCHANGES WON’T SHOW OUTPUT IF SUBSCRIBER DATABASE NAME IS SAME AS PUBLISHER DATABASE NAME

This blog is targeted to Merge replication.

Note: Please note the sp_showpendingchanges is only an estimate and not the actual number of changes so it cannot be used for ensuring consistency. It is based on changes known as per local metadata and doesn’t take into account remote nodes metadata. An alternative way to ensure consistency is to use merge agent parameter –Validate 1 or 3.

This issue happens if you have subscriber database name is same as publisher database name. If you execute sp_showpendingchanges with context of publisher database or subscriber database and it would not display any rows.

This issue happens because we specifically check within sp_showpendingchanges if subscriber database name is same as publisher database name.

This behavior of SP_SHOWPENDINGCHANGES is same in SQL Server 2005 and SQL Server 2008.

ALTERNATE WAY OF VALIDATION:

Include following parameters in merge agent Run Agent step (for more information on Replication Merge Agentrefer to https://msdn.microsoft.com/en-us/library/ms147839.aspx ):

-Validate [0|1|2|3]
Specifies whether validation should be done at the end of the merge session, and, if so, what type of validation. The value of 3 is the recommended value.

-ValidateInterval validate_interval
Is how often, in minutes, the subscription is validated in continuous mode. The default is 60 minutes.

How to add merge agent’s parameters:

1) Identify merge agent for publisher database.

--This is the distribution database name on my end.

USE Distribution
GO

SELECT name
FROM dbo.MSmerge_agents WHERE publisher_db = 'SHOWPENDINGCHANGES' --SHOWPENDINGCHANGES is the publisher database name.
GO

Output:

SUNSERV1\SQL2K5_1-SHOWPENDINGCHANGES-SHOWPENDINGCHANGES_PU-SUNSERV1\SQL2K5_2-9

2) Above query will display the respective merge agent name, locate merge agent job under SQL Server Agent è Jobs è Right Click è Select Properties è Click on Steps è 2^nd Step “Run Agent” è Click on Edit Button è On next window add parameters in Command (Each parameter and its value is separated with single space).

If you add following merge agent parameters depending upon -ValidateInterval value (The default is 60 minutes) you can see following message in merge agent verbose log.

Examples:

{ -Output E:\MergeVerbose.txt -OutputVerboseLevel 2 -Validate 1 -ValidateInterval 1

Following is the message you see in merge agent verbose log with above parameters:

Article 't1' passed data validation (rowcount only).

-Output E:\MergeVerbose1.txt -OutputVerboseLevel 2 -Validate 2 -ValidateInterval 1

Following is the message you see in merge agent verbose log with above parameters:

Article 't1' passed data validation (rowcount and checksum).

-Output E:\MergeVerbose1.txt -OutputVerboseLevel 2 -Validate 3 -ValidateInterval 1

Following is the message you see in merge agent verbose log with above parameters:

Article 't1' passed data validation (rowcount and checksum).

}

REFERENCES:

sp_showpendingchanges (Transact-SQL)

SQL Server 2005

https://msdn.microsoft.com/en-us/library/ms186795(SQL.90).aspx

SQL Server 2008

https://msdn.microsoft.com/en-us/library/ms186795.aspx

Replication Merge Agent

https://msdn.microsoft.com/en-us/library/ms147839.aspx

RECOMMENDED READING FOR MORE INFORMATION:

Following MSDN articles would help if you have an application to validate data at subscriber.

Validating Replicated Data

https://msdn.microsoft.com/en-us/library/ms152758(SQL.90).aspx

How to: Validate Data at the Subscriber (SQL Server Management Studio)
https://msdn.microsoft.com/en-us/library/ms151251(SQL.90).aspx

How to: Validate Data at the Subscriber (Replication Transact-SQL Programming)
https://msdn.microsoft.com/en-us/library/ms147366(SQL.90).aspx

How to: Validate Data at the Subscriber (RMO Programming)
https://msdn.microsoft.com/en-us/library/ms147892(SQL.90).aspx

Sanjiv Gore
3rd Tier Support Engineer, Microsoft SQL Server

Reviewed By

Amit Banerjee
Technical Lead, Microsoft Sql Server