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