4

I am trying to repair a suspect database. The problem is that when I run:

EXEC sp_resetstatus property;

it gives me this response:

Warning: You must recover this database prior to access.

when I try this:

ALTER DATABASE property SET EMERGENCY;

it also fails:

Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'property', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

what do I do next? Thanks.

  • What caused this database to become suspect? Did you move any file of it? Are all the files at their location? – sepupic Jun 9 '17 at 6:46
  • I found another topic where you say it was SQL Server crash. So you didn't touch any file, right? Please check the errorlog where it's mentioned WHY the server marked this database as suspect – sepupic Jun 9 '17 at 7:55
2

Its good to restore from an earlier backup copy.If you dont have one..below are the steps that can be done to repair suspect database..this has been explained with more examples by Paul Randal here :Creating, detaching, re-attaching, and fixing a SUSPECT database

Overview of steps

  1. set suspect database offline
  2. Copy those mdf ldf files to some location
  3. drop the old suspect database
  4. create a new database with same name and same file layout
  5. set this new database offline
  6. delete the newly created mdf,ldf files and copy old corrupted files
  7. now try bringing the database online and try repairing with DBCC

below are the scripts

CREATE DATABASE DEMOSUSPECT

ALTER DATABASE DEMOSUSPECT SET OFFLINE

--- you have to copy and drop the old database files prior to this
----NOW DELETE THE mdf and ldf files created for new database

--copy those corrupted files into new location
alter database demosuspect set online

 --check status once
SELECT DATABASEPROPERTYEX (N'DemoSuspect', N'STATUS')

--tyr putting the database into emergency mode and use DBCC
ALTER DATABASE [DemoSuspect] SET EMERGENCY;
GO
ALTER DATABASE [DemoSuspect] SET SINGLE_USER;
GO
DBCC CHECKDB (N'DemoSuspect', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO


-- Check the state
SELECT DATABASEPROPERTYEX (N'DemoSuspect', N'STATUS') AS N'Status';
GO
| improve this answer | |
2

Try This:

EXEC sp_resetstatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
DBCC checkdb([YourDatabase])
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET MULTI_USER
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.