MSreplication_queue getting dropped in SQL 2005

I have set up an Updateable Subscriber replication between databases A and B.
This creates and uses the MSreplication_queue system table in the destination
(B) database.
I have lot of other tables being replicated from A to B, all standard
Transactional replication. e.g. Table C is replication from A to B,
transaction replication. When I dropped the publication for table C from A,
it dropped the following objects in B as well:
MSreplication_queue
MSrepl_queuedtraninfo
This caused the Updateable Subscription to fail. I got around this by
creating another (dummy) Updateable Subscriber replication which recreated
the above tables, and then dropped the dummy publication.
This sounds like a serious bug, because why would dropping a simple
transactional replication drop these two tables, especially since dropping an
UPDATEABLE SUBSCRIBER replication doesn't touch these tables AND a
transactional publication has nothing to do with these tables ?
We did not experience this problem in SQL 2000.
Has anyone else come across this problem ? Is there a fix for it, as it's
causes a serious problem is our organisation because a critical process
depends on the updateable subsciption working.
Yes this seems to be an issue with the way the subscription is dropped,
please open a case with PSS so that we can look into this
"Pranil" wrote:

> I have set up an Updateable Subscriber replication between databases A and B.
> This creates and uses the MSreplication_queue system table in the destination
> (B) database.
> I have lot of other tables being replicated from A to B, all standard
> Transactional replication. e.g. Table C is replication from A to B,
> transaction replication. When I dropped the publication for table C from A,
> it dropped the following objects in B as well:
> MSreplication_queue
> MSrepl_queuedtraninfo
> This caused the Updateable Subscription to fail. I got around this by
> creating another (dummy) Updateable Subscriber replication which recreated
> the above tables, and then dropped the dummy publication.
> This sounds like a serious bug, because why would dropping a simple
> transactional replication drop these two tables, especially since dropping an
> UPDATEABLE SUBSCRIBER replication doesn't touch these tables AND a
> transactional publication has nothing to do with these tables ?
> We did not experience this problem in SQL 2000.
> Has anyone else come across this problem ? Is there a fix for it, as it's
> causes a serious problem is our organisation because a critical process
> depends on the updateable subsciption working.

MSmerge_tombstone confusion

We have merge replication set up between a SQL Enterprise server and an
MSDE instance. We modify both databases through an inhouse app.
Things were great until after one synch a whole bunch of rows
mysteryously disapeared from the server. The kicker was that MSDE
instance had no changes made to it, when I looked at the job history I
discovered that the MSDE had apparently uploaded 40k some odd deletes.
Fortunatly I had a backup from that morning so there was no real loss
outside a chilling feeling that I couldn't trust replication. Today
I've been experimenting with various fixes I found in the MS
knowledgebase (I set 'compensate_for_errors' to false). I've got a few
upfront questions. When we first set up replication we planned to only
replicate the smaller tables as the larger tables wouldn't fit the
MSDE, later we filtered the larger tables and included them in
replication. The end result is that we have some 131 tables who's
identity columns are 'Not for Replication' and 9 tables whose identity
columns apparently are being replicated. Also all the foriegn keys are
being replicated. Both of these things seem to be often mentioned as
problematic for replication but I'm not really sure why. Thats all
background relating to my confusion over how replication works. I set
up a subscription and got my first replication fired off and running.
When it weas finished I poked around on the servers tombstone table,
lots of records as I expected. Then I looked at the tombstone on the
subscriber, empty also as expected. I then ran my second synch and was
very suprised to find that the tombstone table on the subscriber was
now filled with records. Will these records upload and delete data on
the server at the next synch?
Thanks, Phil Howard
Phil,
I have a proc on my website (url below, in the scripts section) that'll help
you to determine which records will be synchronized from the subscriber.
As for identities, they'll need to be set for NFR, so the replication
process can do an identity insert. Also, they must be partitioned - either
manually or using the automatic option. If you do a manual partitioning and
set the publisher to use odds and the subscriber evens (assuming one
subscriber only) then you can subsequently forget about it. If using
automatic range management you set up a range of 10,000,000 values, you can
usually forget about it also.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Msg 3710, Level 16, State 1, Line 1

Hello Folks,

I am moving the model & Msdb databases to a different location and I have this error.

Thanks

Msg 3710, Level 16, State 1, Line 1

Cannot detach an opened database when the server is in minimally configured mode.

I was able to fix it, I used an ALTER DATABASE statement.

Thanks for the time.

|||What command are you using...the Alter DB stmt only works with TEMPDB...I thought? I am getting "

Cannot detach an opened database when the server is in minimally configured mode." as well. I set the trace flag -T3608 and I get the message when I exec sp_detach_db 'msdb'

|||I was able to resolve this error by restarting SQL Server

Msg 3710, Level 16, State 1, Line 1

Hello Folks,

I am moving the model & Msdb databases to a different location and I have this error.

Thanks

Msg 3710, Level 16, State 1, Line 1

Cannot detach an opened database when the server is in minimally configured mode.

I was able to fix it, I used an ALTER DATABASE statement.

Thanks for the time.

|||What command are you using...the Alter DB stmt only works with TEMPDB...I thought? I am getting "

Cannot detach an opened database when the server is in minimally configured mode." as well. I set the trace flag -T3608 and I get the message when I exec sp_detach_db 'msdb'

|||I was able to resolve this error by restarting SQL Server
|||Mkae sure to set the SQL server Service to Automatic mode and restart the SQL Server Service resolved my problem.

MSDTC IN WIN 2003 CLUSTER

Hi Guys,
rirht now my databases running on two seperate sql servers
server1 and server2 and ecah having MSDTC .
Now i am planning to shift these two servers into a
cluster.
1)shall i go for active/passive or active/active.
2)if active/active then it will be named virtual instance
will it require any modification in the application other
than changing the connection string.
3)in any case how MSDTC will get affected in the new
cluster.
4) how sql server will identify the MSDTC AS it is having
different network name, ip address tec
pls advice.
Answers inline...
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Bijupg" <anonymous@.discussions.microsoft.com> wrote in message
news:173e01c51e37$f62aecd0$a501280a@.phx.gbl...
> Hi Guys,
> rirht now my databases running on two seperate sql servers
> server1 and server2 and ecah having MSDTC .
> Now i am planning to shift these two servers into a
> cluster.
> 1)shall i go for active/passive or active/active.
Do your databases require the complete power of the current machines? Will
the clustered machines be the same power or more powerful then the current
machines? What about current RAM and clustered RAM?

> 2)if active/active then it will be named virtual instance
> will it require any modification in the application other
> than changing the connection string.
You can only have one default instance in a SQL cluster. If you need two
instances, at least one will be named.

> 3)in any case how MSDTC will get affected in the new
> cluster.
MSDTC will be shared. Follow 817064 and then 301600.

> 4) how sql server will identify the MSDTC AS it is having
> different network name, ip address tec
> pls advice.
>
That is all part of the SQL magic and clustering. Ok, so I have no idea, but
I know it works well
|||As for your MSDTC here is the basic best pratice on this
When clustering MS DTC it is preferred to have it in its own group with its
own resources, your second choice should be to put it in the cluster group,
use the quorum disk and create MS DTC its own Network Name and IP Address
resources for your MSDTC resource to use and set that resources properties
not to affect the group.
Here are some KB articles on correctly setting this up:
301600 How to configure Microsoft Distributed Transaction Coordinator on a
http://support.microsoft.com/?id=301600
817064 How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/?id=817064
Also this article which may be of interest:
817065 How To Enable Network COM Access in Windows Server 2003
http://support.microsoft.com/?id=817065
Dave Whitney
SQL Support