MSRepl_Commands table is huge - 456Gb

Question

I have a "MSRepl_Commands" table which has grown up to 456Gb in size. It keeps growing everyday. Can someone help me finding a fix or remedy to purge this table, please?

 

Work done so far:

• Updated the sys.publications to set immediate_sync & allow_anonymous properties to FALSE and also set the Subscriptions Expire retention to 48hrs.

 

• Ensured that the delete procs "sp_MSdelete_publisherdb_trans" and "sp_MSdelete_dodelete" are altered as ‘Delete Top 100000’.

• Ensured that the "Distribution cleanup" and "Agent history cleanup" jobs are running properly.

 

 

Any assistance and / guidance here is highly solicited and will be appreciated.

Answer 1

Is your distribution cleanup task running successfully?

It seems that this job has not been able to keep up with the commands which are being written here.

It will take quite some time to delete all these commands. Is there anyway you can drop all subscribers, publications, clear out these tables and start again?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Hilary,

Yes, The cleanup task is running successfully.

I do not want to reconfigure the replication. I am looking for a remedy that can help purging the "MSRepl_Commands" table.

 

Answer 3

If your cleanup task is working successfully then you are likely encountering a bug.

Is this SQL 2005 SP2?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

Both the Publisher and Distributor are SQL 2008 (SP1).

Answer 5

Are all of your subscribers replicating successfully? What is your command retention period?

use sp_helpdistributor for this and post back the values for:

min distrib retention
max distrib retention
history retention

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 6

Yes, Hilary! All my subscribers are in sync.

The retention details are:

min distrib retention  = 0
max distrib retention = 72
history retention = 48

Answer 7

can you do this for me?

use distribution

GO

select msrepl_transactions.publisher_Database_id, count(*) from msrepl_transactions (nolock)
join msrepl_commands (nolock)
on msrepl_transactions.publisher_database_id=
msrepl_commands.publisher_database_id
group by msrepl_transactions.publisher_Database_id
order by 2 desc

This may take quite some time to run.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 8

Hi Hilary,

I am haivng same issue where the msrepl_commands table is getting huge. What does the query you gave do?

Is it possible to have orphaned data in this table that has not been cleaned up? And is there a way to identify the orphan data in this table?

Thanks

Answer 9

The query will list publications by the number of commands in the queue (msrepl_commands). You can use this as a basis of figuring out what the problem with the publisher is - ie is it defunct/orphaned, etc. Does it have anonymous subscribers, or subscribers who are not syncing.

I don't run into problems with pure transactional replication not cleaning up - there were problems with p2p, but they have been fixed now.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 10

Ok thanks will let you know what the result is. So does msrepl_commands table contain the commands that have been relpicated successfully or does it contain just a queue of the commands?

And what data is in the msrepl_transactions table? Does the entry_time field show the dates for all commands in the msrepl_commands table?

 

 

 

 

 

 

 

Answer 11

Sort of, msrepl_commands is a queue which replication reads and replays the commands on the subscribers. These commands should be cleaned up after they have been applied.  The log reader agent writes to this queue.

Msrepl_transctions is a table of transactions. The commands in msrepl_commands are grouped by transactions in msrepl_transactions. So a transaction is written to msrepl_transactions and the constituent commands which form that transaction are stored in msrepl_commands.

 

Yes the entry_time does reflect when the transaction was written to msrepl_transactions.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 12

So is there any way to tell when the msrepl_commands table still has commands that have already been applied and not been deleted from the table?

 

 

Answer 13

Try something like this:

declare @Publisher_database_ID int
set @Publisher_database_ID  =1
declare @max_cleanup_xact_seqno varbinary(16)  
exec sp_MSmaximum_cleanup_seqno @Publisher_database_ID , NULL, @max_cleanup_xact_seqno OUTPUT
print @max_cleanup_xact_seqno

select Count(*) from MSrepl_commands with (nolock) where
            publisher_database_id = @Publisher_database_ID  and
            xact_seqno <= @max_cleanup_xact_seqno

Do this for each Publisher_ID.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 14

Sorry for the delayed response Hilary!

 

Following are the details:

 

query:

use distribution

GO

select msrepl_transactions.publisher_Database_id, count_big(1) from msrepl_transactions (nolock)
join msrepl_commands (nolock)
on msrepl_transactions.publisher_database_id=
msrepl_commands.publisher_database_id
group by msrepl_transactions.publisher_Database_id
order by 2 desc

Result:

publisher_Database_id    (No column name)
6                                      278441020246151840
7                                      17364948
12                                    424988
5                                      92990
8                                      32000
21                                    43
22                                    6
16                                    3

 

FYI  - The associated database (publisher_database_id = 6) has 19 publications.

Please let me know if you need any further details.

 

Answer 15

It's not necessary to mark a thread (or even a post) as abusive when someone else tags onto your original post. A gentle reminder to stay on topic or start another thread is often all that is needed. It creates a lot of work for Moderators to clean up the mess. Tagging onto a thread may not be useful, but it does not satisfy the 'abusive' test -in my opinion.

---

"You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson

Answer 16

Understood. Thank you, Arnie!

Can someone help me in this matter?

WHY THE MSREPL_COMMANDS TABLE KEEPS ON GROWING LARGER?

WHAT CAN BE DONE TO REMEDIATE THE ISSUE?

Answer 17

Hi Vijay,

I believe this is a bug that has resurfaced SQL 2008 SP1 during the distribution cleanup process, specifically in sp_MSmaximum_cleanup_seqno.  You can find a description of the problem here.

I suggested contacting Microsoft Support or posting an item on Microsoft Connect | SQL Server to get the issue addressed in a timely manner.  You can also try applying the latest service pack and see if that alleviates the issue.

---

Brandon Williams (blog | twitter)