1

I have a runaway distribution database that is 640gb in size. I have narrowed it down to the msreplcommands table which is holding 2 billion rows.

I have checked all publications and found 2 of them having immediate sync set to 1. I changed this to 0. Reran the cleanup job and it deleted about 240K rows only. Ran this:

SELECT *  from MSrepl_transactions (nolock) where entry_time < getdate() -5;

Found entries as old as 30 days.

The problem is I am running out of space very fast and all delete attempts lock the msreplcommands table and thus lock replication. We are a 24/7 shop and I have no server downtime possible. Is there another way to delete this amount of data without locking replication?

Hi T.H, Thanks for the suggestion. I found that link earlier and decided to try it. I've done some investigating and found very old entries in the MSrepl_transactions table going back 32 days using the following query:

SELECT distinct cast(entry_time as date) as entry_time, publisher_database_id, xact_seqno, count(publisher_database_id) FROM [distribution].[dbo].MSrepl_transactions group by entry_time, publisher_database_id, xact_seqno order by entry_time

Improve this question
7
  • Have you tried the process at blogs.msdn.microsoft.com/chrissk/2009/12/29/…? Or are you saying even those built-in commands lock the table in an unacceptable way?
    – T.H.
    Commented Feb 22, 2017 at 12:05
  • Hi T.H. I used the link you posted and modified the statement:
    – ManuelRdgz
    Commented Feb 22, 2017 at 14:49
  • But have you tried the SP call of which there is an example in step 8? In your case to first try to get rid of anything older than 30 days you would specify max_distretention = 720.
    – T.H.
    Commented Feb 22, 2017 at 14:55
  • I used the link you posted and found transactions older than 32 days. I modified the statement: max_distretention = 744 I've run it and its been running now for 7 hour and 40 min without locking but hasn't completed. Commands are still going up. Sorry for bad formatting first time posting here.
    – ManuelRdgz
    Commented Feb 22, 2017 at 14:56
  • Hey T.H. Update!! At exactly the 8 hour mark the commands are starting to decrease in MSrepl_commands! HURRAY! Thanks. It is going very slowly but its also looking through a 2 billion row table. Its also not locking anything! I'll keep monitoring and add another update here later.
    – ManuelRdgz
    Commented Feb 22, 2017 at 15:14

1 Answer 1

Reset to default
1

The process at https://blogs.msdn.microsoft.com/chrissk/2009/12/29/how-to-resolve-when-distribution-database-is-growing-huge-25gig/ can help with this.

To prevent it happening in future you may need to look at the configuration of the Distribution clean up: distribution job.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.