Record Log Reader Commands

How fast is your replication actually flowing? This stored procedure answers that by scraping the MSlogreader_history tables on every distributor database in the instance, computing the number of commands delivered per hour for each publication, and MERGing the results into a persistent LogreaderCommands table - so you can chart replication throughput over time. It handles multiple distribution databases with dynamic SQL, buckets history into hourly TimeBlocks keyed by distribution db and publication db, filters out negative deltas caused by stopped agents, and cleans up rows older than 365 days. Pair it with the companion maintenance job to run it every minute.

CREATE PROCEDURE RecordLogreaderCommands AS
BEGIN--RecordLogreaderCommands
	--This procedure scrapes the log-reader agent logs and saves the history for reporting purposes.
	
	set nocount on;

	IF OBJECT_ID('dbo.LogreaderCommands') IS NULL
	BEGIN--IF
		CREATE TABLE [dbo].[LogreaderCommands]
		(
			[TimeBlock] [datetime] NOT NULL,
			[DistributionDb] [varchar](128) NOT NULL,
			[PublicationDb] [varchar](128) NOT NULL,
			[Commands] [bigint] NULL,
			CONSTRAINT [PK_LogreaderCommands] PRIMARY KEY CLUSTERED 
			(
				[TimeBlock] ASC,
				[DistributionDb] ASC,
				[PublicationDb] ASC
			)
		)
	END--IF

	CREATE TABLE #Commands
	(
		DistributionDb varchar(128),
		PublicationDb varchar(128),
		Time datetime,
		Commands bigint
	)

	DECLARE @Database varchar(128)
	DECLARE @tSQL varchar(MAX)


	declare @RankedCommands table
	(
		Id int,
		DistributionDb varchar(100),
		PublicationDb varchar(128),
		Time datetime,
		Commands bigint
	)

	DECLARE curDatabases CURSOR for
		select
			name
		from
			sys.databases
		where
			is_distributor = 1
	OPEN curDatabases

	FETCH FROM curDatabases INTO @Database

	WHILE(@@FETCH_STATUS = 0)
	BEGIN--WHILE
		------------------------------------------------------------------------------------------------
		
		set @tSQL = 
		'insert into #Commands' +
		' select' +
		' ''' + @Database + ''' as DistributionDb,' +
		' Agents.publisher_db as PublicationDb,' +
		' History.time as Time,' +
		' History.delivered_commands as Commands' +
		' from' +
		' [' + @Database + '].dbo.MSlogreader_history as History' +
		' inner join [' + @Database + '].dbo.MSlogreader_agents as Agents' +
		'    on Agents.id = History.agent_id'
		EXEC(@tSQL)

		------------------------------------------------------------------------------------------------
		FETCH FROM curDatabases INTO @Database
	END--WHILE

	CLOSE curDatabases DEALLOCATE curDatabases

	insert into @RankedCommands
	select
		row_number() over (order by Time) AS Id,
		DistributionDb,
		PublicationDb,
		Time,
		Commands
	from
		#Commands

	drop table #Commands

	select
		First.Id,
		First.DistributionDb,
		First.PublicationDb,
		First.Time,
		DateDiff(second, First.Time, (select top 1 Next.Time from @RankedCommands as Next
			where Next.Id > First.Id
			and Next.DistributionDb = First.DistributionDb
			and Next.PublicationDb = First.PublicationDb
			order by Next.Id asc)) as Seconds,
		((select top 1 Next.Commands from @RankedCommands as Next
			where Next.Id > First.Id
			and Next.DistributionDb = First.DistributionDb
			and Next.PublicationDb = First.PublicationDb
			order by Next.Id asc) - First.Commands) as Commands
	into
		#Report
	from
		@RankedCommands as First
	order by
		First.Time

	select
		TimeBlock,
		DistributionDb,
		PublicationDb,
		Sum(Commands) as Commands
	into
		#TimeBlock
	from 
	(
		select
			DistributionDb,
			PublicationDb,
			Commands,
			Convert(VarChar, DATEPART(Month, Time)) + '/' +
			Convert(VarChar, DATEPART(Day, Time)) + '/' +
			Convert(VarChar, DATEPART(Year, Time)) + ' ' +
			Convert(VarChar, DATEPART(Hour, Time)) + ':00:00' as TimeBlock
		from 
			#Report
	) as report
	where
		Commands is not null
	group by
		DistributionDb,
		PublicationDb,
		TimeBlock
	having
		Sum(Commands) > 0 --If the log reader is stopped, the count would drop below zero for one log line.
	order by
		DistributionDb,
		PublicationDb,
		TimeBlock

	drop table #Report

	MERGE INTO LogreaderCommands AS [Target] USING
		(
			SELECT
				[Source].TimeBlock,
				[Source].DistributionDb,
				[Source].PublicationDb,
				[Source].Commands
			FROM
				#TimeBlock AS [Source]
		) AS [Source]
		ON [Source].TimeBlock = [Target].TimeBlock
		AND [Source].DistributionDb = [Target].DistributionDb
		AND [Source].PublicationDb = [Target].PublicationDb
	
	WHEN MATCHED THEN
		UPDATE SET	
			Commands = [Source].Commands

	WHEN NOT MATCHED BY TARGET THEN
		INSERT
		(
			TimeBlock,
			DistributionDb,
			PublicationDb,
			Commands
		)
		VALUES
		(
			[Source].TimeBlock,
			[Source].DistributionDb,
			[Source].PublicationDb,
			[Source].Commands
		)
	;

	drop table #TimeBlock

	--Cleanup records over xx days old	
	delete from
		LogreaderCommands
	where
		DATEDIFF(d, TimeBlock, GETDATE()) > 365
END--RecordLogreaderCommands
GO