I have transactional replication. I have number of publications filtered by
key value. Each publication has one push subscriber and doesn't allow pull
and anonymous subscribers. Retention period set to 0 min and 365 days max.
All commands now delivered to subscriber I've checked it with query: select
sum(UndelivCmdsInDistDB), sum(DelivCmdsInDistDB) from
distribution.dbo.MSdistribution_status. Result is 0, 730. I've run job
"Distribution clean up: distribution" to be sure clean up has done. But
nothing changed there are still 730 delivered commands.

How to clean up distributed commands?


--
In the best we trust
Georgy Nevsky

Do you have any anonymous subscribers? If so, the
commands will be retained until the retention period is
reached.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

What do you mean: "Do you have any anonymous subscribers?" ?
How I can check it?

All my publications was created with following options:
.....
exec @L_Result= sp_addpublication
@publication = @L_publication,
@description = @L_description,
@sync_method = 'concurrent',
@repl_freq = 'continuous',
@status = 'active',
@allow_push = 'true',
@allow_pull = 'false',
@allow_anonymous = 'false',
@independent_agent = 'true',
@immediate_sync = 'true',
@allow_sync_tran = 'false',
@autogen_sync_procs = 'false',
@retention = 0,
@allow_queued_tran = 'false',
@snapshot_in_defaultfolder = 'false',
@alt_snapshot_folder = @L_snapshot_path,
@pre_snapshot_script = @L_snapshot_sql,
@compress_snapshot = 'false',
@allow_dts = 'false',
@allow_subscription_copy = 'false',
@add_to_active_directory = 'false',
@logreader_job_name = @L_logreader_job_name
....

"Paul Ibison" <Paul.Ibison (AT) Pygmalion (DOT) Com> wrote

It's OK - you don't as @allow_anonymous is set to 'false'
for all your publications.
You could use sp_browsereplcmds to see what is actually
left in the queue which might clarify things somewhat.
I have come across situations where the commands remain
in msrepl_commands until the retention period is reached.
These seems to be some anomaly that causes this to occur
(I have an open PSS case on it), but as far as I have
seen, and from postings here, this only seems to occur
for extremely large amounts of commands.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Forgot to add this:

SELECT name, allow_anonymous
FROM syspublications

Please run this in the publication db to confirm that
they're all set the same. (no need to post back unless
you see some 1's.

Rgds,
Paul Ibison

with sp_browsereplcmds I found a lot of records like followings:

0x000022540003D19C0015 0 4
200 -2147483646
\\CS-DATA01\CN2_5_Assets_Dev\ttstest\stage\rpl\unc\VSQL DEV$SQLDEV_CNstage2_5_rpl_company_cid_1126\2005011 4103510\ProductData_46.sch
0x000022540003D19C0015 0 4
200 -2147483646
\\CS-DATA01\CN2_5_Assets_Dev\ttstest\stage\rpl\unc\VSQL DEV$SQLDEV_CNstage2_5_rpl_company_cid_1126\2005011 4103510\null_creation_49.sql
0x000022540003D19C0015 0 4
200 -2147483645
sync -t"ProductData" -d"\\CS-DATA01\CN2_5_Assets_Dev\ttstest\stage\rpl\unc\VSQL DEV$SQLDEV_CNstage2_5_rpl_company_cid_1126\2005011 4103510\ProductData_45.bcp"
-hORDER( [CID],[ProductID],[AttributeID],[DataID] ASC)
0x000022540003D19C0015 0 4
200 -2147483578 exec sp_MSdefer_check @objname =
N'ProductData'
0x000022540003D2440001 0 4
161 -2147483611 SYNCSTAT

I guess they are related to initilisation of subscriber.

But also there are many regular commands like these:
0x000022540003D3BB0005 0 4 172
30 {CALL rplMS_Company_upd
(NULL,NULL,0,NULL,'123',0,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,1126,0x1000)}
0x000022540003D3BE0004 0 4 172
30 {CALL rplMS_Company_upd
(NULL,NULL,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,1126,0x1000)}



"Paul Ibison" <Paul.Ibison (AT) Pygmalion (DOT) Com> wrote

Georgy - if all of the subscribers for each publication have synchronized,
then there's not a lot you can do. One posibility is to synchronize often
(this is a must) and reduce the retention period down to a day, which, if
the distribution cleanup agent is running regularly, should do the trick.
Once the backlog is cleared, then the retention period can be increased. To
be honest, for such a small no of commands, I probably wouldn't bother.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Actually this is just developer database with very few activities. On
production site there are more than 4 millions records stucked on
distributor.
Also in my situation I can't run synchronization very often by system
design. So I'm looking for solution for my issue. I'll create my own cleanup
procedure if I won't find another solution....

I'm going to create my own cleanup procedure. Please clarify: is it enought
to cleanup tables MSrepl_transactions and MSrepl_commands ?

Setting the transaction retention period essentially
achieves the same end (in conjunction with running the
distribution cleanup agent) but through the existing
tried and tested routines, so if you really want to
develop your own procedures, I would only recommend going
down this route with PSS interaction.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)