• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 857
  • Last Modified:

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

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
JRockFL
Asked:
JRockFL
1 Solution
 
LowfatspreadCommented:
consider running

sp_removedbreplication

0
 
JRockFLAuthor Commented:
Thank you for your reply. I tried that and I still get the same error message  :(
0
 
Eugene ZCommented:

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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
JRockFLAuthor Commented:
There are no replications setup. I restored a database that has all the system replicaiton tables. i.e. MSnapshot_history etc..etc.
0
 
Eugene ZCommented:


try:

USE master
GO
EXEC sp_replicationdboption @dbname = N'<Publication database name>', @optname = N'publish', @value = N'false'
0
 
nito8300Commented:
Did you try detaching or taking the database offline and then deleting?
0
 
shanncanCommented:
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
 
Eugene ZCommented:
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
 
JRockFLAuthor Commented:
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
 
Eugene ZCommented:
so what about plan B'?
0
 
Eugene ZCommented:
did you try:

use master
go
exec sp_removedbreplication 'yourDB'
go
drop database 'yourDB'
0
 
JRockFLAuthor Commented:
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
 
Eugene ZCommented:
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
 
Eugene ZCommented:
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
 
JRockFLAuthor Commented:
EugeneZ,

Thank you so much! Plan B worked!!!
0
 
JRockFLAuthor Commented:
Thank you for your dedication to helping me solve this problem.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now