SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cannot drop the database XXX because it is being used for replication.


Cannot drop the database XXX because it is being used for replication.

Author
Message
e.francone
e.francone
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 200

When I try to drop the XXX database SQLServer returns me this error message:

Server: Msg 3724, Level 16, State 3, Line 1
Cannot drop the database XXX because it is being used for replication.


I've also tried to execute the following code but nothing has changed.

exec sp_removedbreplication 'XXX'
go
exec sp_dboption 'XXX','published',false
go
exec sp_dboption 'XXX','merge publish',false
go

The XXX DB is a restore of "Distribution" database of a Production Server that is a publication server!

Someone can help me?

Thanks for all suggestions!!!

Frank





Rodrigo Acosta
Rodrigo Acosta
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4531 Visits: 22

Please, run sp_helpreplicationdb to check with databases are marked for replication.

Distribution is for replication but should not be marked for replication, so you should be able to restore it at any time.





e.francone
e.francone
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 200

I want to drop XXX Db that contains a restore of "distribution" database of a production server. I've tried to use sp_helpreplicationdb but I'm not be able to drop XXX DB.

Can you give me some more advices?

Thanks.

Frank





Christopher Klein
Christopher Klein
Mr or Mrs. 500
Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)

Group: General Forum Members
Points: 590 Visits: 226

what type of replication?

What I've had to run to make sure my scripts are starting clean for merge replication are as follows:

'Drop any subscriptions
exec sp_dropmergesubscription @publication = N'<publication>''

'Drop the publication
exec sp_dropmergepublication @publication = N'<publication>'

'Set the DB to not be for replication
exe sp_replicationdboption @dbname= N'<db>' @optname=N'merge publish' @value = 'FALSE'

If you're using trans rep I believe the commands are similar.

Also if you go into EM and right-click the publication you can choose 'generate script' which will give you the option to see the script for creating or droping the publications.

regards,

Chris





majbou
majbou
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 1

although it's not recommended, sometimes you 'll have to update the REPLINFO column in SYSOBJECTS.

Maj.





Nonenone-147172
Nonenone-147172
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 1

You can restore another database backup, that don`t have replications configured, over the database that you want to delete(with the "force restore over existing database" option marked). Doing this you will overwrite the sysobjects lines that saves the replication parameters and the SQL Server will alow you to delete the database.

I hope It`s easy to understand..

Mila.


e.francone
e.francone
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 200
Nonenone!! Thanks a lot!!!



Raja Abubaker-455153
Raja Abubaker-455153
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 42

take the datbase offline and then drop it.

raja


JeremyE
JeremyE
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5908 Visits: 4088
Set Category for the database to 0 in master.dbo.sysdatabases. Then you can delete it.
casia
casia
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 5
Hi
I have the same problem
I tried the following

update sysdatabases
set Category = 0
where dbid = 13

but I get the following error
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search