Calculate Subscription Expiration Threshold

While I was piecing together a few queries for a report intended to deliver subscription information about subscribers that are nearing the expiration threshold for replication I found myself needing to calculate warning threshold levels. In one case I found not only do I need to calculate the threshold warning criteria but I also needed to round the outcome to the nearest whole integer.

As you probably know the default retention period for replication defaults to 14 days; however, in replication monitor you can set up warnings to indicate if a subscriber is nearing the threshold. Typically this is 80% so in short I am going to take the retention value and multiple it by .8 and round to the nearest whole integer. For this example I really only need to query data from one table [MSreplication_monitordata] which resides within the distribution database. If you have replication setup then you will have a distribution database, but if you don’t then the distribution database will not be available. Also note this example is based on a single publication so if you have multiple publications then you will see more than one entry per subscriber per publication.

Step 1: Simple Query

I just want the retention period…

SELECT retention 
FROM [Distribution]..[MSreplication_monitordata] 

Now we know what the retention period is set to and that is 14 days. Now I need to multiply the retention value by .8 and the result will be 11.2. So 80% of 14 = 11.2.

Step 2: Simple Calculation

SELECT retention
      ,(retention * .8) 
FROM [Distribution]..[MSreplication_monitordata]

Side Note:

Keep in mind if you want to get the threshold value of the metric being monitored from the database JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id and pull the t.[value] which will return the value of 80 which is the default. If it was changed then of course the value will differ. Just note that this is a sql_variant data type so you will need to CAST or CONVERT it to perform the calculation.

SELECT	m.retention 
		,t.[value]
		-- These steps are just for illustration
		,(CAST(t.[value] AS DECIMAL(10,2))/100) '1'
		,(m.[retention] * (CAST(t.[value] AS DECIMAL(10,2))/100)) '2'
		,CAST((m.[retention] * (CAST(t.[value] AS DECIMAL(10,2))/100)) AS INT) '3'
		-- The following is faster and easier
		,(m.retention * CAST(t.[value] AS INT)/100) '4'
FROM [Distribution]..[MSreplication_monitordata] m
JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id

What this means in terms of Replication Monitor is that any subscriber that has not synchronized for 11 days with the retention set to 14 days will have a subscription status of “Expiring Soon/Expired“. If the retention was set to 30 days which is also common and the threshold set to the default of 80% then the subscribers would show the same the status when they reach 24 days without synchronizing.

Step 3: Round the Results

11.2 is a good start, but I need a whole number so I will wrap line 2 from (Step 2) inside a ROUND function and supply 0 for the length or precision as it needs one argument.

SELECT retention
      ,ROUND((retention * .8) ,0)
FROM [Distribution]..[MSreplication_monitordata]

Step 4: Remove the Decimal

The above statement returns a value of 11.0 which is rounded, but now I need to get rid of the decimal place so that 11 is the only thing returned. Again I will take line 2 this time from (Step 3) and wrap it in a CAST function and CAST it as INT.

SELECT retention
      ,CAST(ROUND((retention * .8) ,0) AS INT)
FROM [Distribution]..[MSreplication_monitordata]

Or you could have saved yourself a few steps and done this…

 
SELECT retention 
      ,CAST((retention * .8) AS INT) 
FROM [Distribution]..[MSreplication_monitordata] 

Final Results

When I apply the rounded value to my query as part of the WHERE clause only the records that fall within the expiration threshold (retention * .8) will be returned. Meaning subscriptions that are close to expiration and subscriptions that are expired.

The results are exactly what I wanted. Now I just need to take it a bit further… think email delivery!

I have wrapped this into a stored procedure and added some logic that will check for expiring records and if there are records found it will then tie the results into a nice html formatted message delivered by database mail. Then I setup a sql job which runs the stored procedure daily including weekends and fires off an email only when records are found. Technically I could fire off a report subscription which can be formatted very nicely and just fire off the job using exec sp_start_job @JobName = ‘Report Subscription ID goes here’ when ever records are detected.

For more information about using ROUND please visit: http://msdn.microsoft.com/en-us/library/ms175003.aspx

Download the code

SELECT.GetExpiringSubscriptions_v0.01.sql

/*****************************************************************
**	Author	:	Samson J. Loo (justsamson.com | @just_samson)
**	Created : 9/26/2010
**	Intent	: Notification of subscriptions that are nearing the
**						the expiration threshold
**	Notes	: Requires db mail
**	Version	: 0.01
*****************************************************************/

IF OBJECT_ID('sp_RP_GetExpiringSubscribers') IS NOT NULL
DROP PROCEDURE sp_RP_GetExpiringSubscribers
GO
CREATE PROCEDURE [dbo].[sp_RP_GetExpiringSubscribers]
AS

DECLARE @cnt INT
SET @cnt = 0
SELECT	@cnt = COUNT(a.[subscriber_name])
FROM [Distribution]..[MSmerge_sessions] s
	JOIN [Distribution]..[MSmerge_agents] a ON s.agent_id = a.id
	JOIN [Distribution]..[MSreplication_monitordata] m ON s.agent_id = m.agent_id
	JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id
WHERE s.end_time IN
(
	SELECT TOP 1 s1.end_time 
	FROM [Distribution]..[MSmerge_sessions] s1
	WHERE s.agent_id = s1.agent_id
	ORDER BY s1.end_time DESC
) AND DATEDIFF(d,s.[start_time],getdate()) >= CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT)
AND t.[isenabled] = 1
GROUP BY s.[start_time]

IF @cnt > 0
	BEGIN
		
		DECLARE @tableHTML  NVARCHAR(MAX) ;

		SET @tableHTML =
			N'<H1><font color="#FF0000">Expiring Subscription Report</font></H1>' +
			N'<table border="0" cellspacing="2" cellpadding="2">' +
			N'<tr><th bgcolor="#BDBDBD">Subscriber</th>' +
						N'<th bgcolor="#BDBDBD">Status</th>
							<th bgcolor="#BDBDBD">Delivery Rate</th>
							<th bgcolor="#BDBDBD">Last Sync</th>' +
						N'<th bgcolor="#BDBDBD">Duration</th>
							<th bgcolor="#BDBDBD">Conn Type</th>
							<th bgcolor="#BDBDBD">Result</th>
							<th bgcolor="#BDBDBD">Days Behind</th>					
						  <th bgcolor="#BDBDBD">Subscriber Status</th></tr>' +
			CAST ( ( 

		SELECT
		td = CASE
					WHEN CHARINDEX('\',a.[subscriber_name]) > 0 THEN LEFT(a.[subscriber_name],CHARINDEX('\',a.[subscriber_name])-1)
					ELSE a.[subscriber_name]
				END	
			,''
			,td = CASE
				WHEN s.[runstatus] = 3 THEN 'Synchornizing'
				WHEN s.[runstatus] = 5 THEN 'Retrying failed command'
			ELSE 'Not Synchronizing'
			END 
			,''
			,td = CAST(s.[delivery_rate] AS VARCHAR) + ' rows/sec'
			,''
			,td = s.[start_time]
			,''
			,td = CAST((s.[duration]/86400) AS VARCHAR) 
			+ '.' + CAST(REPLACE(STR(((s.[duration]/3600) - ((s.[duration]/86400) * 24)),2),SPACE(1),0) AS VARCHAR)
			+ ':' + CAST(REPLACE(STR((s.[duration] % 3600/60),2),SPACE(1),0) AS VARCHAR)
			+ ':' + CAST(REPLACE(STR((s.[duration] % 60),2),SPACE(1),0) AS VARCHAR)	
			,''
			,td = CASE 
				WHEN s.[connection_type] = 1 THEN 'LAN'
				WHEN s.[connection_type] = 2 THEN 'Dialup'
				WHEN s.[connection_type] = 3 THEN 'Web Sync'
			END
			,''
			,td = CASE
				WHEN s.[runstatus] = 1 THEN 'Start'
				WHEN s.[runstatus] = 2 THEN 'Succeed'
				WHEN s.[runstatus] = 3 THEN 'In Progress'
				WHEN s.[runstatus] = 4 THEN 'Idle'
				WHEN s.[runstatus] = 5 THEN 'Retry'
				WHEN s.[runstatus] = 6 THEN 'Error'
			END
			,''
			,td = DATEDIFF(d,s.[start_time],getdate())
			,''
			,td = CASE 
				WHEN (DATEDIFF(d,s.[start_time],getdate()) < CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT)) THEN 'Good'
				WHEN (DATEDIFF(d,s.[start_time],getdate()) <= m.[retention]) THEN 'Expiring Soon'
				WHEN (DATEDIFF(d,s.[start_time],getdate()) > m.[retention]) THEN 'Expired'
			END
			--,m.[retention]
		FROM [Distribution]..[MSmerge_sessions] s
			JOIN [Distribution]..[MSmerge_agents] a ON s.agent_id = a.id
			JOIN [Distribution]..[MSreplication_monitordata] m ON a.id = m.agent_id
			JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id
		WHERE s.end_time IN
		(
			SELECT TOP 1 s1.end_time 
			FROM [Distribution]..[MSmerge_sessions] s1
			WHERE s.agent_id = s1.agent_id
			ORDER BY s1.end_time DESC
		) AND DATEDIFF(d,s.[start_time],getdate()) >= CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT)
		AND t.[isenabled] = 1
		ORDER BY s.[start_time]    
		    
			FOR XML PATH('tr'), TYPE 
			) AS NVARCHAR(MAX) ) +
			N'</table>' ;

		EXEC msdb.dbo.sp_send_dbmail 
			@profile_name = 'WorkingNotifier',
			@recipients='you@yourdomain.com',
			@copy_recipients='someone@somewhere.com',
			@subject = 'Expiring Subscription Report',
			@body = @tableHTML,
			@body_format = 'HTML' ;
		
		
	END
ELSE
	BEGIN
		PRINT 'No Records Found!'
	END
Advertisements

6 thoughts on “Calculate Subscription Expiration Threshold

  1. objektnichtgefunden says:

    your code is incredibly helpful since the standard agent warning and mail distribution process doen’t seem to work as expected. perhaps you could publish the stored procedure, so all of us can benefit from your wisdom. thanks a million in advance…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s