msreplication_queue table growing on subscriber - why

Have a live and standby server and failed over to the standby after (i
thought) using EM to turn off replication.
Now find out that the ms_replication_table on the subscriber is growing -
what have I missed ?
The very least I need to do is remove/delete this table as I now don't have
enough disk space for a backup and currently no tape drive
Suggestions please
Thanks
Luci
I suppose the question is "do you need to send this data back to the
publisher?". If so, then running the queue reader agent will prepare the
table for truncation. If you aren't interested in this data, then you'll need
to drop the subscription. If the publisher is no longer available, then you
can use sp_removedbreplication to remove this table and any other remnants of
replication.
Cheers,
Paul Ibison
|||Thanks for quick reply Paul.
No we don't need this data (we're not replicating at the moment) not
even sure where its coming from as the original live is not in use !
Tried running sp_removedbreplication on the subscriber yesterday but the
transaction log just doubled ( and we have no space).
To stop it growing then, do I just go on the original publisher and just
drop the subscription - presume thats a an option in EM ? or do I have to run
a stored procedure on there ?
Cheers
Luci
"Paul Ibison" wrote:

> I suppose the question is "do you need to send this data back to the
> publisher?". If so, then running the queue reader agent will prepare the
> table for truncation. If you aren't interested in this data, then you'll need
> to drop the subscription. If the publisher is no longer available, then you
> can use sp_removedbreplication to remove this table and any other remnants of
> replication.
> Cheers,
> Paul Ibison
|||Hi Luci,
I was suggesting sp_removedbreplication for the case where the publisher was
not available, but this should still be ok. If you run sp_dropesubscription
on the publisher we should be back to normal. The balooning of the
transaction log on the subscriber is normal when you are deleting so much
data. You might want to backup the log and then shrink it, or if the log is
not required for the backup strategy, you could use simple recovery mode and
then shring the log file.
HTH,
Paul Ibison
|||Thanks again Paul
Plan to do this tonight out of hours tonight, going with your second
suggestion by
putting db in simple mode and trying sp_removedbreplication on the
subscriber first.
Cheers
Luci
PS Glad you answered as I found this forum through your replicationanswers
website Thank you again !
"Paul Ibison" wrote:

> Hi Luci,
> I was suggesting sp_removedbreplication for the case where the publisher was
> not available, but this should still be ok. If you run sp_dropesubscription
> on the publisher we should be back to normal. The balooning of the
> transaction log on the subscriber is normal when you are deleting so much
> data. You might want to backup the log and then shrink it, or if the log is
> not required for the backup strategy, you could use simple recovery mode and
> then shring the log file.
> HTH,
> Paul Ibison
>
|||What I'd probably try is to delete the queue records in batches. The batch
size could be 10000 and each time round the loop you backup the log with
truncate_only set. this way the records will get removed without balooning
the log, and you can drop the subscription at the end...
HTH,
Paul Ibison
|||Hi
Just to let you know we got there in the end.
Ended up truncating the msreplication_queue_table, then tried
sp_removedbreplication again which failed as it couldn't drop tables and the
tempdb couldn't extend. So, manually dropped the tables in question, after
which
the sp_removedbreplication did work. Thank you for your help. Cheers Luci
"Paul Ibison" wrote:

> What I'd probably try is to delete the queue records in batches. The batch
> size could be 10000 and each time round the loop you backup the log with
> truncate_only set. this way the records will get removed without balooning
> the log, and you can drop the subscription at the end...
> HTH,
> Paul Ibison
>

MSREPL_Commands TABLE

I don't think I ever resolved this issue & now I am quite concerned about my growing MSRepl_Commands table.
It has over 9 million rows.
What steps can I take to reduce the number of rows in this table?
Thanx!
JUDE
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
"Jude" <judes@.email.uophx.edu> wrote in message news:utbKUSEtHHA.2752@.TK2MSFTNGP06.phx.gbl...
I don't think I ever resolved this issue & now I am quite concerned about my growing MSRepl_Commands table.
It has over 9 million rows.
What steps can I take to reduce the number of rows in this table?
Thanx!
JUDE
|||Expect this if you have anonymous subscribers. If this is a problem for you
consider using named subscribers if you can.
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
"Jude" <judes@.email.uophx.edu> wrote in message
news:utbKUSEtHHA.2752@.TK2MSFTNGP06.phx.gbl...
I don't think I ever resolved this issue & now I am quite concerned about my
growing MSRepl_Commands table.
It has over 9 million rows.
What steps can I take to reduce the number of rows in this table?
Thanx!
JUDE

MSmerge_tombstone growing and growing

I have merge replication setup up for 6 SQLCE Subscribers.
I have noticed that the MSmerge_tombstone table is growing
at a fast rate regardless of any changes to the data in
the database. It seems to be consistantly adding 50 rows
of data to the table every 2 minutes. As the table grows
it causes the SQLCE subscirbers to fail with the following
message:

ERROR: -2147467259
SQL Server Reconciler failed: Run

ERROR: -2147200925
: Failed to enumerate changes in the filtered
articles.

ERROR: 0
: {call sp_MSsetupbelongs
(?,?,?,?,?,0,?,?,1,?,?,?,?,?,?)}

ERROR: 0
: The merge process timed out while executing a
query. Reconfigure the QueryTimeout parameter and retry
the operation.

I'm sure that this is due to the size of the
MSmerge_tombstone.

Should the MSmerge_tombstone table grow at this rate?
36,000 rows every 24hrs!

I understand there is the sp_mergecleanupmetadata Stored
procedure but if i use this does that mean that because i
have to reinitialise all the subscribers, they are going
to have to pull down the whole subscription again.

I have since Changed a settings to make subscription
expiration date to 8 days instead of never expires but
we're still getting 50 rows added every 2 minutes

SQL SERVER 2000 SP3
Hope someone can shed some light on this for me.

Thanks.
.What is the level of service pack on SQL & SQL CE?
What is the value set for retention period of the publication?|||Satya,

SQL Server is SP3
SQL CE is just SQLCE1.0 devices running PPC2002

The subscription expiration was set to never expire, however this morning i have changed to publication properties to 8 days. I haven't reinitialised anything after this change.|||May try with reintialization and see whether error persists.

Have you tried sp_mergecleanupmetadata to clean up the data?|||I did try this sp last week when the table had grown to just under 1 million rows, however I was forced to reinitialise the subscriptions.

I will probably run it agian at the end of play today and then get them to resync in the morning. And then see if the change in retention has done anything.

running the sp and reinitialising on a regular basis is going to be difficult as the devices are synchronising throughout the day and I can't get them all in to upload their changes first. Plus I don't want them to download the whole subscription again. 5mb over a fairly slow link to the server.

Do you think 50 rows every 2 minutes is unusual behaviour. Or is this to be expected?

Thanks,

Tim|||On the terms of speed 50rows every 2 min. is no issue, as CE devices are meant to be slower as compared to Desktops/servers.

And may try contacting MS PSS to get hold of QFE build number 765 to fix the issue.