locked
SQL DB in Suspect State RRS feed

  • Question

  • I run

    EXEC sp_resetstatus property;

    It gives me this warning:

    Warning: You must recover this database prior to access.

    I also tried to set the database in EMERGENCY mode, which also fails

    Msg 5011, Level 14, State 7, Line 1
    User does not have permission to alter database 'Database_Name', 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.

    Thanks,


    Tuesday, August 7, 2018 8:14 AM

All replies

  • I run

    EXEC sp_resetstatus property;

    It gives me this warning:

    Warning: You must recover this database prior to access.

    I also tried to set the database in EMERGENCY mode, which also fails

    Msg 5011, Level 14, State 7, Line 1
    User does not have permission to alter database 'Database_Name', 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.

    Thanks,

    Good day ?!?,

    1. Confirm that you are using a user that is member in the sysadmin

    2. Fix any issue in the database before executing this SP

    3. Try to restart the server


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, August 7, 2018 8:33 AM
  • I run

    EXEC sp_resetstatus property;

    It gives me this warning:

    Warning: You must recover this database prior to access.

    I also tried to set the database in EMERGENCY mode, which also fails

    Msg 5011, Level 14, State 7, Line 1
    User does not have permission to alter database 'Database_Name', 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.

    Thanks,


    So you are saying your SQL Server database is in suspect mode ? If so are you able to run checkdb on the database ?. Do you have clean SQL Server backup. 

    I believe a suspect database can be put into emergency mode. Please see Paul Randal's Blog

    ALTER DATABASE [db_name] SET EMERGENCY;
    GO
    ALTER DATABASE [db_name] SET SINGLE_USER;
    GO
    DBCC CHECKDB (N'Db_name', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    GO
    NOTE: ONLY run above statement if you do not have backup, repair allow data loss can cause data loss so be careful. Restarting would not help in case of suspect database.

     You need to have sysadmin permission in sql server to run above commands


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, August 7, 2018 9:05 AM
  • I run

    EXEC sp_resetstatus property;

    It gives me this warning:

    Warning: You must recover this database prior to access.

    I also tried to set the database in EMERGENCY mode, which also fails

    Msg 5011, Level 14, State 7, Line 1
    User does not have permission to alter database 'Database_Name', 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.

    Thanks,


    Error 5011 is related to permission, run the following query and share the output:
    SELECT IS_SRVROLEMEMBER('sysadmin') as I_am_SA, * FROM sys.databases WHERE name = 'Database_Name'



    Tuesday, August 7, 2018 9:40 AM
  • I run

    EXEC sp_resetstatus property;

    It gives me this warning:

    Warning: You must recover this database prior to access.

    I also tried to set the database in EMERGENCY mode, which also fails

    Msg 5011, Level 14, State 7, Line 1
    User does not have permission to alter database 'Database_Name', 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.

    Thanks,


    Error 5011 is related to permission, run the following query and share the output:
    SELECT IS_SRVROLEMEMBER('sysadmin') as I_am_SA, * FROM sys.databases WHERE name = 'Database_Name'



    The output is 1
    I tried to detach the database and this error message returned by database:
    Msg 3707, Level 16, State 2, Line 1
    Cannot detach a suspect or recovery pending database. It must be repaired or dropped

    Tuesday, August 7, 2018 11:33 AM
  • Yeah, dont have updated backup and data_loss is looking risky. 
    Tuesday, August 7, 2018 11:59 AM
  • I run

    EXEC sp_resetstatus property;

    It gives me this warning:

    Warning: You must recover this database prior to access.

    I also tried to set the database in EMERGENCY mode, which also fails

    Msg 5011, Level 14, State 7, Line 1
    User does not have permission to alter database 'Database_Name', 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.

    Thanks,

    Good day ?!?,

    1. Confirm that you are using a user that is member in the sysadmin

    2. Fix any issue in the database before executing this SP

    3. Try to restart the server



    Answers

    Yes, member in sysadmin

    No, I do not fix any issue before using this SP

    Didn't try to restart server

    Tuesday, August 7, 2018 12:03 PM
  • Yeah, dont have updated backup and data_loss is looking risky. 

    Then there are 2 methods

    1. Run below command to put database in emergency mode. This will allow you to access database.

    ALTER DATABASE [DATABASE_NAME] SET EMERGENCY
    GO 

    Now goto SSMS and you would be able to expand database. Script out the tables and schema structure and create a new database with this script. Now use import export wizard to move data to new database created. Later drop suspect database and rename old database to new.

    2. Or you may try some third party tool


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by Xi Jin Wednesday, August 8, 2018 2:11 AM
    Tuesday, August 7, 2018 1:22 PM
  • Thank you so much guys for helping me. @shanky_621 (MCC, partner, MVP), fixed problem by third party software. 

    Great forum and amazing support.

    Thanks again

    Isabella Garcia (SQL DBA)

    Wednesday, August 8, 2018 4:48 AM