MSDN:
sp_publication_validation - Initiates an article validation request for each article in the specified publication. This stored
procedure is executed at the Publisher on the publication database.
sp_publication_validation [ @publication = ] 'publication'
[ , [ @rowcount_only = ] type_of_check_requested ]
[ , [ @full_or_fast = ] full_or_fast ]
[ , [ @shutdown_agent = ] shutdown_agent ]
[ , [ @publisher = ] 'publisher' ]
sp_publication_validation is used in transactional replication.
sp_publication_validation can be called at any time after the articles associated with the publication have been activated. The procedure
can be run manually (one time) or as part of a regularly scheduled job that validates the data.
If your application has immediate-updating Subscribers, sp_publication_validation may detect spurious errors. sp_publication_validation
first calculates the rowcount or checksum at the Publisher and then at the Subscriber. Because the immediate-updating trigger could
propagate an update from the Subscriber to the Publisher after the rowcount or checksum is completed at the Publisher, but before the
rowcount or checksum is completed at the Subscriber, the values could change. To ensure that the values at the Subscriber and Publisher
do not change while validating a publication, stop the Microsoft Distributed Transaction Coordinator (MS DTC) service at the Publisher
during validation.
Solution:
Create Table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LOG_TRANSACTIONAL_REPLICATION_SUBSCRIBER_DATA_VALIDATION_PROCESSING](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[Publication] [varchar](50) NULL,
[ValidationStartTime] [datetime] NULL,
CONSTRAINT [PK_LOG_TRANSACTIONAL_REPLICATION_SUBSCRIBER_DATA_VALIDATION_HISTORY] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Create Table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LOG_TRANSACTIONAL_REPLICATION_SUBSCRIBER_DATA_VALIDATION](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[AgentID] [int] NULL,
[Name] [varchar](250) NULL,
[PublisherDB] [varchar](50) NULL,
[Publication] [varchar](50) NULL,
[SubscriberDB] [varchar](50) NULL,
[MessageLogTime] [datetime] NULL,
[InsertedDate] [datetime] NULL,
[Details] [varchar](250) NULL,
CONSTRAINT [PK_LOG_TRANSACTIONAL_REPLICATION_SUBSCRIBER_DATA_VALIDATION] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[LOG_TRANSACTIONAL_REPLICATION_SUBSCRIBER_DATA_VALIDATION] ADD CONSTRAINT [DF_LOG_TRANSACTIONAL_REPLICATION_SUBSCRIBER_DATA_VALIDATION_InsertedDate] DEFAULT (getdate()) FOR [InsertedDate]
GO
Create Stored Procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[spaCheckReplicationValidation]
as
--Run Transactional Replication Subscriber Data Validation for all Publications on the server
Set nocount on
--Get last time Validation was run
Declare @LastValidationTime datetime
Select @LastValidationTime = max(ValidationStartTime) from dbo.LOG_TRANSACTIONAL_REPLICATION_SUBSCRIBER_DATA_VALIDATION_PROCESSING
where Publication = 'InsertPublicationNameHere'
Declare @Cnt int, @Pub sysname
Declare @SQL nvarchar (500)
Create Table #Tmp (ID int Identity(1,1), Publication sysname)
Insert into #Tmp select name from ResRT.dbo.syspublications
--Select distinct(publication) from Distribution.dbo.MSdistribution_agents
Select @Cnt = count(*) from #TMP
While @Cnt > 0
Begin
Set @Pub = (select top 1 publication from #Tmp where ID = @Cnt)
--Log processing
Insert into dbo.LOG_TRANSACTIONAL_REPLICATION_SUBSCRIBER_DATA_VALIDATION_PROCESSING (Publication, ValidationStartTime)
Select @Pub, Getdate()
Set @SQL = 'exec <dbnamehere>.dbo.sp_publication_validation @Publication =' + '' + @Pub + '' + ', @rowcount_only = 1, @full_or_fast = 0'
EXEC sp_executesql @SQL
Delete from #Tmp where ID = @Cnt
Set @Cnt = @Cnt -1
End
Drop Table #Tmp
DECLARE @tableHTML NVARCHAR(MAX) ;
--Log each failed Subscriber Data Validation
Insert into dbo.LOG_TRANSACTIONAL_REPLICATION_SUBSCRIBER_DATA_VALIDATION
(AgentID, [Name], PublisherDB, Publication, SubscriberDB, MessageLogTime, Details)
select
t1.agent_id as 'AgentID',
t2.name,
t2.publisher_db,
t2.publication,
t2.subscriber_db,
t1.[Time],
t1.comments as 'Details'
from Distribution.dbo.MSdistribution_history t1 with (nolock)
inner join Distribution.dbo.MSdistribution_agents t2 with (nolock)
on t1.agent_id = t2.id
where runstatus = 3 and (comments not like '%transaction%' and comments like '%out of synchronization%' )
and Publication = 'InsertPublicationNameHere'
and [Time] > @LastValidationTime
--Time = The time the message is logged
--Check for failed since last Validation Time was run
order by [Time] desc
IF @@ROWCOUNT > 0 BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '',
@recipients='',
@subject = 'DB Report: Replication Validation Alert!',
@body = @tableHTML,
@body_format = 'HTML' ;
END
|
|
|
|
|
|