Delete or detach a Suspect database

#1

Everything I read about sp_resetstatus says you must restart SQL after. Is
there a way around that (don't want to shut down production). The suspect
db is just an empty db that is not needed. I need to create another db with
the same name.
Thanks.

#2

rdraider (rdraider (AT) sbcglobal (DOT) net) writes:

Quote:

Everything I read about sp_resetstatus says you must restart SQL after.
Is there a way around that (don't want to shut down production). The
suspect db is just an empty db that is not needed. I need to create
another db with the same name.

I may be missing something, but what about DROP DATABASE? I made a quick
test on SQL 2000 where I created a database, stopped SQL Server, deleted
the log file to make the database suspect, and restarted SQL Server.
DROP DATABASE worked just fine.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#3

No queries would work since this database did not exist in
Master.sysdatabases. Not sure why. Attempting to detach from Enterprise
Manager produced an error. Then I simply closed and reopened EM and it was
gone! Odd.

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Quote:

rdraider (rdraider (AT) sbcglobal (DOT) net) writes:
Everything I read about sp_resetstatus says you must restart SQL after.
Is there a way around that (don't want to shut down production). The
suspect db is just an empty db that is not needed. I need to create
another db with the same name.

I may be missing something, but what about DROP DATABASE? I made a quick
test on SQL 2000 where I created a database, stopped SQL Server, deleted
the log file to make the database suspect, and restarted SQL Server.
DROP DATABASE worked just fine.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#4

rdraider (rdraider (AT) sbcglobal (DOT) net) writes:

Quote:

No queries would work since this database did not exist in
Master.sysdatabases. Not sure why. Attempting to detach from Enterprise
Manager produced an error. Then I simply closed and reopened EM and it was
gone! Odd.

The last thing you should believe is a graphical tool. But they usually
offer a Refresh option to handle such situations.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Delete or detach a Suspect database

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Delete or detach a Suspect database - 09-20-2007 , 12:02 PM

Re: Delete or detach a Suspect database - 09-20-2007 , 04:25 PM

Re: Delete or detach a Suspect database - 09-20-2007 , 08:22 PM

Re: Delete or detach a Suspect database - 09-21-2007 , 02:14 AM