Earlier post had same title just with a little bit difference of giving
subscribers and all subscribers, so sharing a script which you can watch
transactional
replication error for all subscribers and get an alert at the time of
error raise. Before going ahead with this post I would like you to read
my earlier post and share your thoughts there.
So diverting on this post and again sharing script to get the same information, but it for all subscribers, you can also use the parameter of subscriber in where condition to apply filters.
Have you scheduled any such script to get an alert and watch for any transaction replication error? Your ideas and thoughts are most welcome.
So diverting on this post and again sharing script to get the same information, but it for all subscribers, you can also use the parameter of subscriber in where condition to apply filters.
Use distribution GO SELECT ma.publisher_db, ma.publication, ma.subscriber_db, msre.time, msre.error_text FROM msrepl_errors msre INNER JOIN msdistribution_history msh ON ( msre.id = msh.error_id ) INNER JOIN msdistribution_agents ma ON ( ma.id = msh.agent_id ) ORDER BY msre.time DESCThis is just a script which I want to present here. Now how can watch and keep attention when such any error occurs for transactional replication error in last 5 minutes and get an alert for the same with following script which can be scheduled every 5 minutes or whatever frequency as per filter applied of error time in query and run against a distribution database on distributor server,
USE distribution GO DECLARE @body VARCHAR(max), @subject VARCHAR(100), @Publication VARCHAR(50), @SubscriberDB VARCHAR(50), @ErrorText VATCHAR(max) SELECT @Publication = ma.publication, @SubscriberDB = ma.subscriber_db, @ErrorText = msre.error_text FROM msrepl_errors msre INNER JOIN msdistribution_history msh ON ( msre.id = msh.error_id ) INNER JOIN msdistribution_agents ma ON ( ma.id = msh.agent_id ) WHERE msre.time >= Dateadd(minute, -5, Getdate()) -- Capturing for last 5 minutes ORDER BY msre.time DESC SET @subject = 'Error captured for ' + @SubscriberDB + ' subscriber database of ' + @Publication + ' publication' SET @body = @ErrorText -- Sending an email IF ( @subject IS NOT NULL ) BEGIN EXEC msdb.dbo.Sp_send_dbmail @recipients = 'prajapatipareshm@gmail.com', @subject = @subject, @body = @body, @profile_name = '<Profile name>', @body_format = 'HTML'; END
Have you scheduled any such script to get an alert and watch for any transaction replication error? Your ideas and thoughts are most welcome.
Comments
Post a Comment