Solved

Cannot Detach Distribution Database

Posted on 2008-06-19
5
1,836 Views
Last Modified: 2008-06-19
I do not have ANY replication defined on the instance.  None of the Agents are active.  When I try to detach the distribution database I get the message that it cannot be detached because it is being replicated.

I think someone may have been playing with replication in the past and did not clean up after themselves. :( :(

Is there a flag somewhere that I can clear to get rid of this message??  I really need to move the database.

Thank You
0
Comment
Question by:DonKronos
  • 3
  • 2
5 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 21826457
In versions of SQL Server prior to SQL Server 2005, moving or renaming database files required detaching and reattaching the database. Because a replicated database cannot be detached, replication had to be removed from these databases first. Beginning with SQL Server 2005, you can move or rename files without detaching and re-attaching the database, with no effect on replication. For more information about moving and renaming files, see ALTER DATABASE (Transact-SQL).

from http://technet.microsoft.com/en-us/library/ms151740(SQL.100).aspx

also
For Replication Disable..... you click on the replicated database, use
followin menu path

TOOLS --> REPLICATION --> DISABLE PUBLISHING ....

OR alternatively you can use sp_removedbreplication stored procedure.



0
 
LVL 15

Author Comment

by:DonKronos
ID: 21826674
Thanks CS, but here is the problem ...  the database is NOT being replicated.  For the entire instance, there are NO Publishers, No Subscribers, NO Items in any of the Agents, but when I try to detach the database it still says that it is being replicated.  :(

I have looked through master, msdb and distribution and cannot find any "leftover" entries that were not cleared.  However, there has to be something someplace that makes SQl Server think the database is being replicated ???

I select the database in Enterprise Manager and went to TOOLS --> REPLICATION  and the Disable Publishing ...  was greyed out.

Any ideas???
0
 
LVL 31

Accepted Solution

by:
James Murrell earned 500 total points
ID: 21826846
did you run the sp_removedbreplication stored procedure

also check maintenance plans, could be there
0
 
LVL 15

Author Comment

by:DonKronos
ID: 21826892
Yes I ran sp_removedbreplication.

I did find the following:

Database:  master
Table:  sysdatabases
Column: category

For the distribution database, the column has a value of 16.  For all other databases, the column has a value of 0 (zero).  I'm trying to find out what the values in that column mean and how to change them if needed.

I will look in the maintenance plan but it is very simple, i.e., backup all databases everynight at 02:00.

Thanks
0
 
LVL 15

Author Comment

by:DonKronos
ID: 21827035
CS  ...  FOUND THE ANSWER !!!! :) :) :)

From this link I found the meaning of the category column.

http://msdn.microsoft.com/en-us/library/aa260406(SQL.80).aspx

A value of 16 means that the database is involved in both as a publisher and subscriber and something else.  Anyway, there must have been a problem when people were "playing" with the database and the value did not get reset.  

To change the value I did the following.

USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
update sysdatabases set category = 0 where category = 16
GO
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO

I then detached; moved the database; attached the database and everything is FINE.  :) :)

Thanks for your assistance and making me think. :)  
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now