Solved

Cannot drop the database, it's being used for replication

Posted on 2006-07-12
16
832 Views
Last Modified: 2008-01-09
Restored a database, and it looks like a system distributed database, it just has system tables in it. I'm not using replication and now I can't delete the database. There are no items on under replication for publications or subscriptions.

Help
0
Comment
Question by:JRockFL
16 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17092803
consider running

sp_removedbreplication

0
 
LVL 8

Author Comment

by:JRockFL
ID: 17092872
Thank you for your reply. I tried that and I still get the same error message  :(
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17092922

How to manually remove a replication in SQL Server 2000 or in SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;en-us;324401
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 8

Author Comment

by:JRockFL
ID: 17093024
There are no replications setup. I restored a database that has all the system replicaiton tables. i.e. MSnapshot_history etc..etc.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17093354


try:

USE master
GO
EXEC sp_replicationdboption @dbname = N'<Publication database name>', @optname = N'publish', @value = N'false'
0
 
LVL 9

Expert Comment

by:nito8300
ID: 17093355
Did you try detaching or taking the database offline and then deleting?
0
 

Expert Comment

by:shanncan
ID: 17093360
It maybe possible that there is a lock on one of the tables.

Have you tried checking for open handles on any of the tables?

To do this go to Enterprise Manager -> Management-> Locks/Objects
 and check if the database is being used by another process. If it is - then kill the process.

then : in query analyser

drop database <database name>

The actual path to check the locks on the objects that I have written above is just off the top of my head - but you'll know what im talking about when you get down to the 'management' bit.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17093435
Plan B':


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.

0
 
LVL 8

Author Comment

by:JRockFL
ID: 17093477
EugeneZ,
The command executed properly. But I still can't drop it.

shanncan,
there were no open handles. I'm rebooting the server.

nito8300,
It doesnt have take offline as an option. All the tables that are in there are system created tables.
It "thinks" it's a system database or something.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17094676
so what about plan B'?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17094693
did you try:

use master
go
exec sp_removedbreplication 'yourDB'
go
drop database 'yourDB'
0
 
LVL 8

Author Comment

by:JRockFL
ID: 17094839
Yes, I used that...
use master
go
exec sp_removedbreplication 'test'
go
drop database test

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

What else can we try??
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 17094910
plan B':
Create a dummy, temp database called 'test1' .
Backup the database.
Restore the backup over the database you can't delete (you'll have to rename the target file so as to not conflict with your original, dummy, temp database).
 delete both databases
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17094994
Back to plan A":

From the BOL:
To disable replication using the SQL Server Enterprise Manager

  1. Expand a server group, and then expand the Distributor (the server
that contains the distribution database).
  2. Right-click the Replication folder, and then click Disable Publishing.
  3. Complete the steps in the Disable Publishing and Distribution Wizard.
==============
Also:
PRB: You receive error 3724 when you drop a non-replicated object
http://support.microsoft.com/default.aspx?scid=kb;en-us;326352
0
 
LVL 8

Author Comment

by:JRockFL
ID: 17098896
EugeneZ,

Thank you so much! Plan B worked!!!
0
 
LVL 8

Author Comment

by:JRockFL
ID: 17098902
Thank you for your dedication to helping me solve this problem.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Live Storage Vmotion VMs with shared VMDK 10 61
SQL Replication question 9 43
Help creating a spatial object in SQL Server 4 23
Sql query with where clause 2 13
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question