April 1, 2003 at 2:20 pm
I have a database that i was merge replicating to one server and log shipping to another server for reporting. This database was accidentally set up as loading.
Now i can delete it because it says it is replicating, and i cant recover it to take it out of the loading. Any ideas on how to delete
April 1, 2003 at 4:30 pm
You may be able to modify the STATUS value and fake the system into allowing a delete.
Try this (as sa of course):
sp_configure 'allow_updates',1
reconfigure with override
GO
-- change the status of the db to normal status:
UPDATE sysdatabases SET status = 0 WHERE name = <your database name>
GO
sp_configure 'allow_updates',0
reconfigure with override
Sorry that I can't guarantee this one...but since you are planning to delete the database anyway, it shouldn't be too dangerous.
Guarddata-
April 2, 2003 at 8:16 am
that took it out of loading but now it cant be dropped because of replication and when you try to drop replication it says cant because it is loading
April 2, 2003 at 8:40 am
Others are more qualified to help here. Sorry for my lack of experience with the replication. You might take a look at both the syspublications and syssubscriptions tables. They both have a status column that might give you a clue.
If this is the only replication, you might try just turning off the replication for the database. A little drastic, though. There are surely easier ways to fix this one.
Guarddata-
April 9, 2003 at 7:39 am
right idea but needs category set to 0 and mode set to 0 that work
thanks
quote:
You may be able to modify the STATUS value and fake the system into allowing a delete.Try this (as sa of course):
sp_configure 'allow_updates',1
reconfigure with override
GO
-- change the status of the db to normal status:
UPDATE sysdatabases SET status = 0 WHERE name = <your database name>
GO
sp_configure 'allow_updates',0
reconfigure with override
Sorry that I can't guarantee this one...but since you are planning to delete the database anyway, it shouldn't be too dangerous.
Guarddata-
April 9, 2003 at 11:06 am
Have you used Enterprise Manager and looked at the processes to see if you can identify the one that is locking your database? If you can find that one, you can 'KILL' it.
Something else you can try (if you haven't already)...stop the SQL Server services (including the SQLSERVERAGENT service). Restart them and then try deleting the database.
-SQLBill
April 9, 2003 at 12:41 pm
actually i got it finally
by changing the status, mode and category in the sysdatabases table
quote:
Have you used Enterprise Manager and looked at the processes to see if you can identify the one that is locking your database? If you can find that one, you can 'KILL' it.Something else you can try (if you haven't already)...stop the SQL Server services (including the SQLSERVERAGENT service). Restart them and then try deleting the database.
-SQLBill
April 10, 2003 at 3:03 am
try this:
EXEC @retval = sp_replicationdboption '<database>', '<publication type>', 'false'
where <database> is the name of the database you published objects from, and <publication type> is eg. 'merge publish' depending on what replicaton you used.
best regards,
chris.
April 10, 2003 at 2:34 pm
actually that didnt work because it said it was in the middle of a restore.
quote:
try this:EXEC @retval = sp_replicationdboption '<database>', '<publication type>', 'false'
where <database> is the name of the database you published objects from, and <publication type> is eg. 'merge publish' depending on what replicaton you used.
best regards,
chris.
April 14, 2003 at 1:49 am
ok! what about:
exec sp_dboption 'dbname', 'bulkcopy', false?
best regards,
chris.
April 14, 2003 at 7:05 am
Can you detach the database? sp_detach_db
or perhaps some of the sp_dropmergepublication or spdropmergesubscription or sp_droppublisher....
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply