--get list of the possible databases by querying the publisher SELECT * FROM distribution.dbo.MSpublisher_databases --Now figure out what the article number is --the database name there is the database that the publication is in. Replication_Master..sp_helparticle @publication = 'User_Profiles' --Now that you have the publisher database ID (step 1) and the article id (step 2) --get the list of commands. EXEC distribution..sp_browsereplcmds @publisher_database_id = 2, @article_id = 369 --And if you're really lucky, in sqlmonitor you'll get the following: --(Transaction sequence number: 0x00018A0500009072002A00000000, Command ID: 1) --in which case... EXEC distribution..sp_browsereplcmds @publisher_database_id = 2, @article_id = 369, @xact_seqno_start = '0x00018A0500009072002A00000000', @xact_seqno_end = '0x00018A0500009072002A00000000' --(set both start and end to the same value, the one in the error message)
simplified version 2013/07/12
was having more problems.
first, look at commands:
select top 1000 * from dbo.MSrepl_commands ORDER BY command_id DESC
Now, from there we have two options. The first gives you the name of the problem child.
SELECT * FROM distribution.dbo.MSpublisher_databases SELECT * FROM dbo.MSarticles ORDER BY article_id
The second gives you the exact commands run
EXEC distribution..sp_browsereplcmds @publisher_database_id = 6, @article_id = 338, @xact_seqno_start = '0x0005334A0000049B0001', @xact_seqno_end = '0x0005334A0000049B0001' --begin and end seqno will be the same.
and if you need to remove old records because you're running out of space and just did a bunch of changes....
(remembering that it saves all the commands for 72 hours by default)
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 50