Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

This week one of my replication subscriptions expired because I wasn't getting any alerts saying that there was a login error (I've fixed those alerts and the error). What I'd like now is, in the case that this happens again, to be able to send an alert saying that a subscription is about to expire (ie, it will expire in 1 or 2 days). I have an alert set up for when a subscription expires, but this is after the fact. I've looked through sys.messages for any text that has "Expir" in it, but I haven't found an appropriate error code yet. Would anyone be able to point me in the right direction?

Thanks.

share|improve this question
    
is this transactional or merge replication? push or pull? –  Nick Kavadias May 31 '10 at 15:29
    
transactional. push. –  Aaron Jun 3 '10 at 15:05

2 Answers 2

up vote 1 down vote accepted
+150

Here you go... This is the way I would go about it... you will need DB Mail enabled and a profile created and change those sections according to your environement below... also change the @MaxOfflineInHours= variable to whatever amount of hours of inactivity you would like to alert on... Create the stored procedure usp_GetExpiringSubscribersList then place the email script below in a SQL job and schedule it. The email script is designed to return a result set to you via email. Let me know if you have any issues with it and I can work through it with you. Let me know if you need anything else. Thanks! -VM

--==============================================================================

Create Procedure dbo.usp_GetExpiringSubscribersList

AS



declare @maxOffLineInHours int

set @maxOffLineInHours = 72 -- <SET YOUR NUMBER OF HOURS TO ALERT ON HERE>

SELECT srvname,

Max_start_time,

DATEDIFF(hh, Max_start_time, getdate())

FROM distribution.dbo.msmerge_sessions

JOIN ( SELECT agent_id,

Max_start_time = MAX(start_time)

FROM distribution.dbo.msmerge_sessions

GROUP BY agent_id

) AS k ON k.agent_id = 
distribution.dbo.msmerge_sessions.agent_id

AND distribution.dbo.msmerge_sessions.start_time = max_Start_Time

JOIN ( SELECT id AS agent_id,

srvname

FROM distribution.dbo.msmerge_agents

JOIN sys.sysservers ON sys.sysservers.srvid = distribution.dbo.msmerge_agents.Subscriber_ID

) AS l ON l.agent_id = k.agent_id

WHERE DATEDIFF(hh, max_start_time, GETDATE()) > @maxOffLineInHours 

ORDER BY DATEDIFF(hh, max_start_time, GETDATE()) DESC 





--====================================================================

-- Put this code in a SQL Job to run daily or whatever interval suits you



EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'AdventureWorks2008R2 Administrator',

@recipients = 'VinnyAdmin@Adventure-Works.com',

@query = 'exec dbo.usp_GetExpiringSubscribersList' ,

@subject = 'usp_GetExpiringSubscribersList',

@attach_query_result_as_file = 1 ;
share|improve this answer
    
I will try out this solution and let you know how it works. Thanks! –  Aaron Jun 4 '10 at 19:31
    
those tables look like the ones used for merge replication :( –  Nick Kavadias Jun 7 '10 at 4:55

The event id I believe you are looking for is 14160. You can set up a SQL Server Agent alert to fire when this event occurs, and you can configure the Agent to email or page you.

Here's the info about the event: http://msdn.microsoft.com/en-us/library/aa337416.aspx

Here's some instructions in setting up an alert: http://technet.microsoft.com/en-us/library/ms175076.aspx

share|improve this answer
    
The threshold [%s:%s] for the publication [%s] has been set. One or more subscriptions to this publication have expired. This event will fire when the subscription has already expired...i'm looking for more of a warning so that i can manually run the subscription before it is expired. –  Aaron Jun 4 '10 at 19:30
    
You can set a threshold to allow the event to fire ahead of time. "When you enable a warning by using Replication Monitor or sp_replmonitorchangepublicationthreshold, you specify a threshold that determines when a warning is triggered. When that threshold is met or exceeded, a warning is displayed in Replication Monitor, and an event is written to the Windows event log. Reaching a threshold can also trigger a SQL Server Agent alert" There's a link to show you how to do that. –  Brian Knight Jun 4 '10 at 20:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.