Within a Microsoft SQL Transactional Replication setup there is a method to capture Commands in Distribution. These are commands in the Distribution DB that are marked as pending transmission to the designated Subscribers. It is possible to log this data and then report on it using an Aggregated Pivot Query. The query example is below using raw data and pivot data.
Example:
Raw Data:
Select LogID, Subscriber, PendingCMDCount, InsertedDate from dbo.LOG_REPLICATION_UNDISTRIBUTED_COMMANDS
T-SQL Solution: Pivot Data
Select
[Date-Year], [Date-Month], [Date-Day],
[Server-1], [Server-2], [Server-3], [Server-4],
[Server-5], [Server-6], [Server-7], [Server-8]
From
(Select
DATEPART(YY,InsertedDate) as 'Date-Year', DATEPART(M,InsertedDate) as 'Date-Month', DATEPART(D,InsertedDate) as 'Date-Day',
Subscriber, SUM(PendingCMDCount) as 'PendingCMDCount' from
dbo.LOG_REPLICATION_UNDISTRIBUTED_COMMANDS t1
Group By DATEPART(YY,t1.InsertedDate), DATEPART(M,t1.InsertedDate), DATEPART(D,t1.InsertedDate), Subscriber) t2
Pivot
(SUM(PendingCMDCount)
FOR t2.[Subscriber] IN ([Server-1], [Server-2], [Server-3], [Server-4],
[Server-5], [Server-6], [Server-7], [Server-8])
) AS pvt
ORDER BY [Date-Year], [Date-Month], [Date-Day]
|
|
|
|
|
|