[Replication] more replication trouble tracking

--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:

use distribution 

 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