OK, I am new to sqlserver. Anyway, I was trying to open up one of my db and it had database_suspect next to it and no items listed under it.
How do I resolve this to bring the db available once again. Thanks.Try this code:
sp_resetstatus [ @.DBName = ] 'database'
If you you on BOL and type in Suspect it will outline the steps that you need to take.
Thanks
Lystra|||...I don't think you want to reset the databases status (ti can be dangerous). you should look to restore the database...|||i got same prob few days ago. the below failed to restore in my case but it is said that its success is about 90%
good luck
*********************************************
-- Important!!!
-- There are 3 levels of repair that can be done.
-- You try step 1 = REPAIR_FAST first, if it does not work (db is still suspect in Enterprice Manager)
-- then you should stop service and start service again before continue with next step.
-- Next step 2 = REPAIR_REBUILD. If that does not work (still suspect) then you stop and start service.
-- Do NOT skip the stop and start service step between each time you try a higher level of repair. SQL needs to be reset.
-- Step 3 if the other 2 do not work is REPAIR_ALLOW_DATA_LOSS. There has been very few times when this one has not been sufficient.
-- It is normally not actual data that dissapears if REPAIR_ALLOW_DATA_LOSS can fix the problem but the other steps cannot
-- but rather primary keys, foreign keys, default constraints or indexes. There is no guarantee though.
-- This is step 1 - run this in master in query analyser and replace 'ips' with your database name
-- Note: this is name as seen in Enterprice Manager and not physical file name
dbcc checkdb ('ips',REPAIR_FAST)
-- This is step 2 - Do the same as step 1 (don't forget to stop and start service before)
dbcc checkdb ('ips',REPAIR_REBUILD)
-- Now starts step 3. Actual data CAN be lost with this command but it usually doesn't happen. Make sure you have copy
-- of the physical files (BOTH .mdf and .ldf!) before you run it. To only have copy of .mdf is sometimes enough, sometimes not, have BOTH!
-- To be able to run level 3, database must be in single user mode
-- Use this command in query analyser to set db as Single User mode. You should use it Directly after you have stopped and restarted
-- the SQL service so that the query analyser session you execute it in is the only user.
sp_dboption 'ips', 'single user', 'TRUE'
-- Step 3 - replace name the same as step 1
dbcc checkdb ('ips',REPAIR_ALLOW_DATA_LOSS)
-- If the repair works, then you can set back single user mode with this command
-- You can also go to database properties in Enterprice Manager
sp_dboption 'ips', 'single user', 'false'
-- resets the suspect status bit. Normally doesn't work but can always try. This command forces the suspect bit to off
-- stop and start service after and look in enterprice manager. If SQL cannot read database, then it willl set back
-- the suspect bit automatically.
exec sp_resetstatus 'ips'
-- Some more misc scripts that can be useful
/*
EXEC sp_detach_db 'inbound_mike', 'true' -- true and false apply to option 'skipchecks', loses replication!!!
EXEC sp_detach_db 'inbound', 'true' -- true and false apply to option 'skipchecks', loses replication!!!
-- after attach, users in that database will be un-usable. Run sp_dropuser 'ips' after and go into Enterprise Manager and re-create.
sp_dropuser 'ips'
EXEC sp_attach_db @.dbname = N'inbound_mike',
@.filename1 = N'd:\mssql7\data\inbound_Data_mike.mdf',
@.filename2 = N'd:\mssql7\data\inbound_Log_mike.ldf'
EXEC sp_attach_db @.dbname = N'SODUAT',
@.filename1 = N'e:\mssql7\data\SODUAT_Data.mdf',
@.filename2 = N'e:\mssql7\data\SODUAT_Log.ldf'
RESTORE DATABASE ips FROM DISK = 'd:\ipsdatabasedump.bak' -- must drop rep. regardless
WITH MOVE 'ips' TO 'g:\mssql7\data\ips.mdf',
MOVE 'ips_log' TO 'c:\mssql7\data\ips_log.ldf'
ALTER DATABASE Tempdb Modify file
( NAME = 'tempdev', FILENAME = 'e:\mssql7\data\ips_.mdf')
ALTER DATABASE Tempdb Modify file
( NAME = 'templog', FILENAME = 'h:\mssql7\data\tempdb.ldf')
exec xp_cmdshell 'net start "pcANYWHERE Host Service"'
exec xp_cmdshell 'net stop "pcANYWHERE Host Service"'
exec xp_cmdshell 'dir d:\mssql7\backup'
exec xp_cmdshell 'dir c:\mssql7\data'
exec xp_cmdshell 'del c:\mssql7\devtools\samples\backup\*.* /q' -- q = quiet, don't ask for confirmation when *.*
*/
