From: jimbo on
On a SQL 2005 distributor we are seeing very high I/O levels and high cpu
when the distribution clean up job runs. The MSrepl_commands table has some
8+ million rows. The MSrepl_transactions table has very old dates in it and
I'm wondering if the clean up job isn't doing what it's supposed to do. Is
there a way I can remove rows from these tables safely?
Thanks!
From: Hilary Cotter on
If you know they have been already distributed to all of the subscribers you
can go into this table and remove them in a simple delete statement.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



"jimbo" <crutch(a)newsgroups.nospam> wrote in message
news:1794737B-8EE3-45E3-948B-5FE5E0578559(a)microsoft.com...
> On a SQL 2005 distributor we are seeing very high I/O levels and high cpu
> when the distribution clean up job runs. The MSrepl_commands table has
> some
> 8+ million rows. The MSrepl_transactions table has very old dates in it
> and
> I'm wondering if the clean up job isn't doing what it's supposed to do.
> Is
> there a way I can remove rows from these tables safely?
> Thanks!


From: Paul Ibison on
You might want to reduce the transaction retention period if it is set too
high.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com


From: jimbo on
The transaction retention is 72 hours and the history retention is 5 days. I
do find transactions more than a year old in the MSrepl_transactions table.
Is this set too high?
Thanks!

"Paul Ibison" wrote:

> You might want to reduce the transaction retention period if it is set too
> high.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>
From: jimbo on
How would I confirm this? A lot of the transaction dates pre-date my
inheritance of these databases by more than a year. Isn't the clean up agent
supposed to take care of these?
Thanks!

"Hilary Cotter" wrote:

> If you know they have been already distributed to all of the subscribers you
> can go into this table and remove them in a simple delete statement.
>
> --
> Hilary Cotter
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
>
>
> "jimbo" <crutch(a)newsgroups.nospam> wrote in message
> news:1794737B-8EE3-45E3-948B-5FE5E0578559(a)microsoft.com...
> > On a SQL 2005 distributor we are seeing very high I/O levels and high cpu
> > when the distribution clean up job runs. The MSrepl_commands table has
> > some
> > 8+ million rows. The MSrepl_transactions table has very old dates in it
> > and
> > I'm wondering if the clean up job isn't doing what it's supposed to do.
> > Is
> > there a way I can remove rows from these tables safely?
> > Thanks!
>
>
>