MSrepl_commands question

Hi,
I have 750,000 commands in the table MSrepl_commands. The
cleanup agent hasn't removed them, although they have all
gone to my one subscriber, and I don't have anonymous
subscribers. What am I missing??
Rgds,
BB
You should be able to delete them manually. You will have to delete them
from the msrepl_transactions and msrepl_commands tables.
Before you do this, I would try to run the clean up manually.
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 0
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:2a4401c4a54e$21714500$a501280a@.phx.gbl...
> Hi,
> I have 750,000 commands in the table MSrepl_commands. The
> cleanup agent hasn't removed them, although they have all
> gone to my one subscriber, and I don't have anonymous
> subscribers. What am I missing??
> Rgds,
> BB
|||Thanks Hilary,
running
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
0, @.max_distretention = 0
has me worried - what if another command comes in just
before I run it - presumably it will be deleted?
Also, have you ever seen this behaviour before - ie
commands retained in this table? Any idea what causes it?
Thanks,
BB
|||only commands that are replicated to the subscriber will be deleted.
Undistributed commands will not.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:43d401c4a55b$1749b550$a401280a@.phx.gbl...
> Thanks Hilary,
> running
> EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
> 0, @.max_distretention = 0
> has me worried - what if another command comes in just
> before I run it - presumably it will be deleted?
> Also, have you ever seen this behaviour before - ie
> commands retained in this table? Any idea what causes it?
> Thanks,
> BB
>
|||btw you can always pipe the msrepl_commands and msrepl_transaction tables to
backup tables before you do the deletes or manually run the clean up
command.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:43d401c4a55b$1749b550$a401280a@.phx.gbl...
> Thanks Hilary,
> running
> EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
> 0, @.max_distretention = 0
> has me worried - what if another command comes in just
> before I run it - presumably it will be deleted?
> Also, have you ever seen this behaviour before - ie
> commands retained in this table? Any idea what causes it?
> Thanks,
> BB
>
|||Thanks Hilary - any idea why it happens?
TIA,
BB
|||Sometimes the distribution clean up agent gets locked and is picked as the
dead lock victim. This normally leads to even more locking as the cleanup
agent has to do more work. You can try to run your distribution agent at a
greater frequency, ie every minute, or stop your log reader and distribution
agent and then run it.
On high volume systems when you constantly get this type or error you might
consider a remote distributor.
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:2bed01c4a564$10f7bb80$a501280a@.phx.gbl...
> Thanks Hilary - any idea why it happens?
> TIA,
> BB
|||I think you would be best to use a maxium_retention period of 24. Paul
Ibison pointed out to me that a value of 0 will remove undistributed
transactions.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:43d401c4a55b$1749b550$a401280a@.phx.gbl...
> Thanks Hilary,
> running
> EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
> 0, @.max_distretention = 0
> has me worried - what if another command comes in just
> before I run it - presumably it will be deleted?
> Also, have you ever seen this behaviour before - ie
> commands retained in this table? Any idea what causes it?
> Thanks,
> BB
>
sql