DonKronos
asked on
Cannot Detach Distribution Database
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
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
ASKER
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???
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???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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. :)
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. :)
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.