Articles with Missing Filter Procedures

A diagnostic query that finds replicated articles whose row filter procedures have gone missing from the publisher database.

Published Feb 15, 2009

When the Log Reader Agent fails with an error like "The Log Reader Agent failed to construct a replicated command from log sequence number (LSN)...", one common root cause is an article that still references a row filter procedure which no longer exists in the published database. This script runs at the publisher: it joins sysarticles to syspublications and left-joins sys.objects on the filter object id, then returns only the rows where the filter procedure name could not be resolved - flagged as "## MISSING!! ##".

/*
	This is very useful when diagnosing Log Reader errors such as:
		The Log Reader Agent failed to construct a replicated command from log sequence number (LSN)ï
*/

USE [Published_Database_Name]
GO

select * from (
		select
			pubs.name AS Publication,
			art.name as Article,
			art.pubid as PublicationId,
			art.artid as articleId,
			art.filter as FilterProcId,
			isnull(obj.name,'## MISSING!! ##') as FilterProcName
		from
			sysarticles as art
		inner join syspublications as pubs
			on pubs.pubid = art.pubid
		left outer join sys.objects obj
			on art.filter = obj.object_id
		where
			art.filter > 0
	)
	as results
where
	FilterProcName='## MISSING!! ##'