How to search for replication information messages e.g "The initial snapshot for publication is not yet available."

Question

How to search for replication information messages e.g "The initial snapshot for publication is not yet available."

DennisPost

SSCrazy

Points: 2691
More actions
April 23, 2014 at 7:08 am

#288160

I have been working a lot with replication the last few weeks. The replications I was working on are all running fine.
Today I got passed a question about missing data. Turns out another servers publication had a subscriber waiting for an initial snapshot.
Unfortunately, replication monitor doesn't give you any indication that a subscription is waiting for a snapshot.
Only after deep investigation did I find the cause of the missing data.
I would really like to set up some kind of alerting explicitly for this scenario, but I don't know where to find this message.
I have been looking in the logs, but runs for more that 5 mins. before I cancel. Too long for alerting purposes.
EXEC sp_readerrorlog 0, 1, 'The initial snapshot for publication'
EXEC master.dbo.xp_readerrorlog 0, 1, 'The initial snapshot for publication', 'is not yet available.', '20140418', '20140419', N'desc'
Anyone out there know where I can find this text?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lempster SSCoach Points: 15651 More actions · Answer 1

Replication Monitor does give you that information, but you have to drill-down on the individual tabs. The 'Distributor to Subscriber tab is where you want to look.

Regards

Lempster

DennisPost SSCrazy Points: 2691 More actions · Answer 2

Lempster (4/24/2014)
Replication Monitor does give you that information, but you have to drill-down on the individual tabs. The 'Distributor to Subscriber tab is where you want to look.
Regards
Lempster

Thanks for responding.

I know that this is visible in replication monitor.

But where is this message stored?

I want to create an alert when this message appears.

Lempster SSCoach Points: 15651 More actions · Answer 3

:ermm: I was responding to a particular statement in your original question:

DennisPost (4/23/2014)
Unfortunately, replication monitor doesn't give you any indication that a subscription is waiting for a snapshot.

I don't have any replication set up here so I can't check, but my guess is that errors such as this would be stored in the MSrepl_errors table in msdb

Regards

Lempster

DennisPost SSCrazy Points: 2691 More actions · Answer 4

Thanks for pointing me in the right direction.

I ended up turning this into a job.

I'll share it after it's been tried and proven.

SELECT*

FROMDistribution.dbo.MSdistribution_agents DA

INNER JOIN Distribution.dbo.MSdistribution_history DH

ON DA.ID = DH.Agent_ID

WHEREDH.Comments LIKE 'The initial snapshot for article ''%'' is not yet available.'

FROM

Note : The comment text is not the same as what is displayed in Replication Monitor.