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
>