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
>

MSDTC Unavailable Windows 2003

Hi
I have a VB6 windows app which calls a VB6 COM+ application (both running on
machine CHOPGBCOM001) which in turn calls a stored procedure on a remote
machine CANSUR001 but it keeps failing with error "MSDTC on server
'CANSUR001' is unavailable". The COM+ application is configured with
Transaction Support=Required and isolation level set to the default of
serialised (the COM+
app changes it to Read Committed)
* CHOPCOM001 and CANSUR001 both have Windows 2003 SP1 and CANSUR001
is running SQL Server 2000.
* MSDTC is started on both machines
* I have Installed/enabled windows components "Enable network COM+ access"
and "Enable network DTC access" on both machines
* I have configured MSDTC in Component services to use "Network DTC access",
allow outbound and inbound on "Transaction Manager Communication" and set
to "No authentication required"
* Both machines are in the same domain
* No local firewalls are installed
* I've rebooted both machines but same result
* CANSUR001 is NOT part of a cluster
* I've tried running
BEGIN distributed transaction
select * from cansur001.leisure.dbo.member
from SQL Analyser in CHOPCOM001 but get the same result
Any help would be gratefully received
God Bless
RonanMSDTC is turned off by default in Windows 2003. Have you enabled it in
Windows?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ronan" <Ronan@.discussions.microsoft.com> wrote in message
news:F77ECB86-A5E1-42CC-9D02-51CEBFDED779@.microsoft.com...
> Hi
> I have a VB6 windows app which calls a VB6 COM+ application (both running
> on
> machine CHOPGBCOM001) which in turn calls a stored procedure on a remote
> machine CANSUR001 but it keeps failing with error "MSDTC on server
> 'CANSUR001' is unavailable". The COM+ application is configured with
> Transaction Support=Required and isolation level set to the default of
> serialised (the COM+
> app changes it to Read Committed)
>
> * CHOPCOM001 and CANSUR001 both have Windows 2003 SP1 and CANSUR001
> is running SQL Server 2000.
> * MSDTC is started on both machines
> * I have Installed/enabled windows components "Enable network COM+ access"
> and "Enable network DTC access" on both machines
> * I have configured MSDTC in Component services to use "Network DTC
> access",
> allow outbound and inbound on "Transaction Manager Communication" and
> set
> to "No authentication required"
> * Both machines are in the same domain
> * No local firewalls are installed
> * I've rebooted both machines but same result
> * CANSUR001 is NOT part of a cluster
> * I've tried running
> BEGIN distributed transaction
> select * from cansur001.leisure.dbo.member
> from SQL Analyser in CHOPCOM001 but get the same result
>
> Any help would be gratefully received
> God Bless
> Ronan|||Distributed Transaction Coordinator service is enabled and started on both
machines
--
Ronan
"Ronan" wrote:

> Hi
> I have a VB6 windows app which calls a VB6 COM+ application (both running
on
> machine CHOPGBCOM001) which in turn calls a stored procedure on a remote
> machine CANSUR001 but it keeps failing with error "MSDTC on server
> 'CANSUR001' is unavailable". The COM+ application is configured with
> Transaction Support=Required and isolation level set to the default of
> serialised (the COM+
> app changes it to Read Committed)
>
> * CHOPCOM001 and CANSUR001 both have Windows 2003 SP1 and CANSUR001
> is running SQL Server 2000.
> * MSDTC is started on both machines
> * I have Installed/enabled windows components "Enable network COM+ access"
> and "Enable network DTC access" on both machines
> * I have configured MSDTC in Component services to use "Network DTC access
",
> allow outbound and inbound on "Transaction Manager Communication" and s
et
> to "No authentication required"
> * Both machines are in the same domain
> * No local firewalls are installed
> * I've rebooted both machines but same result
> * CANSUR001 is NOT part of a cluster
> * I've tried running
> BEGIN distributed transaction
> select * from cansur001.leisure.dbo.member
> from SQL Analyser in CHOPCOM001 but get the same result
>
> Any help would be gratefully received
> God Bless
> Ronan|||One thing I forgot to mention is that the remote server CANSUR001 is a
Domain Contoller, anyone come across problems running distributed
transactions against
servers which are also doiman controllers?
Ronan
"Ronan" wrote:
> Distributed Transaction Coordinator service is enabled and started on both
> machines
> --
> Ronan
>
> "Ronan" wrote:
>

MSDTC Unavailable

Hi
I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
calls a COM+ component which in turn calls another COM+ component ( data
access module) which in turn calls a stored procedure on another machine
CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC o
n
server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on bot
h
PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
in both directions. DTCtester fails with the same message as above. On the
Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
to "allow inbound" and "allow outound" and "no authentication required". I'v
e
run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
account) to CANPAD001 but again it gives the same error.
Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
Any help would be much appreciated
RonanI forgot to mention there is no firewall on either machine and all the ports
are open.
I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
Ronan
"Ronan" wrote:

> Hi
> I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
> calls a COM+ component which in turn calls another COM+ component ( data
> access module) which in turn calls a stored procedure on another machine
> CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC
on
> server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on b
oth
> PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works o
k
> in both directions. DTCtester fails with the same message as above. On th
e
> Win2K3 machine (CANPAD001) I've set the MSDTC setting in component service
s
> to "allow inbound" and "allow outound" and "no authentication required". I
've
> run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the
sa
> account) to CANPAD001 but again it gives the same error.
> Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
> Any help would be much appreciated
> --
> Ronan|||Also in the SQL Server logs for CANPAD001 I have the following message.
CANPAD001 is not part of a a cluster.
"Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"
Ronan
"Ronan" wrote:
[vbcol=seagreen]
> I forgot to mention there is no firewall on either machine and all the por
ts
> are open.
> I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
>
> --
> Ronan
>
> "Ronan" wrote:
>

MSDTC Unavailable

Hi
I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
calls a COM+ component which in turn calls another COM+ component ( data
access module) which in turn calls a stored procedure on another machine
CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
in both directions. DTCtester fails with the same message as above. On the
Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
to "allow inbound" and "allow outound" and "no authentication required". I've
run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
account) to CANPAD001 but again it gives the same error.
Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
Any help would be much appreciated
Ronan
I forgot to mention there is no firewall on either machine and all the ports
are open.
I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
Ronan
"Ronan" wrote:

> Hi
> I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
> calls a COM+ component which in turn calls another COM+ component ( data
> access module) which in turn calls a stored procedure on another machine
> CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
> server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
> PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
> in both directions. DTCtester fails with the same message as above. On the
> Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
> to "allow inbound" and "allow outound" and "no authentication required". I've
> run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
> account) to CANPAD001 but again it gives the same error.
> Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
> Any help would be much appreciated
> --
> Ronan
|||Also in the SQL Server logs for CANPAD001 I have the following message.
CANPAD001 is not part of a a cluster.
"Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"
Ronan
"Ronan" wrote:
[vbcol=seagreen]
> I forgot to mention there is no firewall on either machine and all the ports
> are open.
> I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
>
> --
> Ronan
>
> "Ronan" wrote:

MSDTC Unavailable

Hi
I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
calls a COM+ component which in turn calls another COM+ component ( data
access module) which in turn calls a stored procedure on another machine
CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
in both directions. DTCtester fails with the same message as above. On the
Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
to "allow inbound" and "allow outound" and "no authentication required". I've
run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
account) to CANPAD001 but again it gives the same error.
Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
Any help would be much appreciated
--
RonanI forgot to mention there is no firewall on either machine and all the ports
are open.
I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
Ronan
"Ronan" wrote:
> Hi
> I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
> calls a COM+ component which in turn calls another COM+ component ( data
> access module) which in turn calls a stored procedure on another machine
> CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
> server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
> PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
> in both directions. DTCtester fails with the same message as above. On the
> Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
> to "allow inbound" and "allow outound" and "no authentication required". I've
> run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
> account) to CANPAD001 but again it gives the same error.
> Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
> Any help would be much appreciated
> --
> Ronan|||Also in the SQL Server logs for CANPAD001 I have the following message.
CANPAD001 is not part of a a cluster.
"Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"
Ronan
"Ronan" wrote:
> I forgot to mention there is no firewall on either machine and all the ports
> are open.
> I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
>
> --
> Ronan
>
> "Ronan" wrote:
> >
> > Hi
> >
> > I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
> > calls a COM+ component which in turn calls another COM+ component ( data
> > access module) which in turn calls a stored procedure on another machine
> > CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
> > server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
> > PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
> > in both directions. DTCtester fails with the same message as above. On the
> > Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
> > to "allow inbound" and "allow outound" and "no authentication required". I've
> > run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
> > account) to CANPAD001 but again it gives the same error.
> > Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
> >
> > Any help would be much appreciated
> >
> > --
> > Ronan