Solution:
Create Procedure dbo.spaTRepTRXinDistroReport
as
Declare @Total decimal(18,3)
set @Total = (Select count(*) from [dbo].[LOG_TRANSACTIONAL_REPLICATION_TRXINDISTRO])
select t.TrxinDistRange as [Transactions in Distriution], count(*) as [Number of Occurences], convert(decimal(18,3),count(*) / @Total) * 100 as '%'
from (
select
case when trxindistro = 0 then '[Group 1]: 0 TRX'
when trxindistro > 0 and trxindistro < 101 then '[Group 2]: 1-100 TRX'
when trxindistro > 100 and trxindistro < 501 then '[Group 3]: 100-500 TRX'
when trxindistro > 500 and trxindistro < 1001 then '[Group 4]: 500-1000 TRX'
when trxindistro > 1000 and trxindistro < 5001 then '[Group 5]: 1000-5000 TRX'
when trxindistro > 5000 and trxindistro < 10001 then '[Group 6]: 5000-10000 TRX'
when trxindistro > 10000 and trxindistro < 50001 then '[Group 7]: 10000-50000 TRX'
when trxindistro > 50000 and trxindistro < 100001 then '[Group 8]: 50000-100000 TRX'
when trxindistro > 100000 and trxindistro < 1000001 then '[Group 9]: 100000-1000000 TRX'
else 'Over 1000000 TRX' end as TrxinDistRange
from [dbo].[LOG_TRANSACTIONAL_REPLICATION_TRXINDISTRO]
) t
group by t.TrxinDistRange
order by TrxinDistRange
Image #1:
i.e. 88.5% of the time there are 0 Transactions in Distribution.
Note that this is based on a 5-min logging interval for the Distribution database compared to the Subscriber database.
|
|
|
|
|
|