Msreplication_queue is taking more sapce.
-
Wednesday, May 23, 2012 7:18 AMDear All,
We are using Sql server 2005 standard eddition.
We aer maintaining Transactional replication with update.
We are observing Msreplication_queue is increasing.Row count is decreasing and increasing but size of table space is increasing only.Same time we are observing Temp DB space is increasing continuously.
Please help me to find out the root cause and solve this issue.
Thanks in advance.
All Replies
-
Wednesday, May 23, 2012 1:55 PMModerator
This table is used by the queue reader and is part of the updatable subscribers replication method.
It will grow until the data in it is older than the retention period and then it is removed. Ensure that your distribution agent clean job is running and that your publication retention is what you need - IE it should be set to a value the could be the longest time yoru subscriber could be offline for. This might be 3 days, or could be the default of 14 days.
You might also want to check if any missing indexes on this table can help minimize tempdb use.
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
- Proposed As Answer by amber zhangModerator Thursday, May 24, 2012 4:42 AM
- Marked As Answer by amber zhangModerator Wednesday, May 30, 2012 8:10 AM
-
Friday, May 25, 2012 10:35 AM
Hi Hilary ,
Thank you very much for you reply.
We have kept the option for subscription expiry is
"subscriptions never expire,but they can be deactivated until they are reinitialize.
Could you please suggest me on this.
-
Friday, May 25, 2012 1:55 PMModeratorI think you need to set your publication retention to some reasonable value. Is your cleanup task running?
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
-
Saturday, May 26, 2012 12:01 PM
Hi Hillary,
Distribution cleanup task is running fine.It is scheduled for every 10 mins.
After one day Msreplication_queue came to normal size.
- Marked As Answer by amber zhangModerator Wednesday, May 30, 2012 8:10 AM
.png)
