SQL Server 2012 - database in Suspect mode - unable to alter database

Question

I have two databases on a SQL 2012 server that are in suspect mode. I am logged in as sa with sysadmin rights but i'm unable to put the database in Emergency mode or do any Alter database commands. All return a message that User does not have permission to alter database 'dbname', the database does not exist, or the database is not in a state that allows access checks. I could possibly restore from a backup but am hoping to fix the issue. I think it occurred after a power outage that took the server down without proper shutdown. Anything i can try?

Answer 1

Both these messages do not say that DB is suspect, how did you reach to that conclusion, is it showing suspect in SSMS ?. Plus you do not seem to have permission that is why you are getting User does not have permission to alter database 'dbname', the database doe not exist, or the database is not in a state that allow access to checks. this message. You need DB owner permission and to be on safer side sysadmin permission to solve database corruption/suspect thing.

Answer 2

Have you reviewed the SQL Server ErrorLog for some details about the reason? On this the next step depends on.

See also https://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/

Answer 3

When i check the logs i get the following:
Login failed for user "sa". Reason: Failed to open the explicity specified database "dbname". [CLIENT: <local machine>]
Error: 18456 : Severity: 14. State 38

When i try to put into emergency mode - i get the following:

MSG 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'dbname', the database doe not exist, or the database is not in a state that allow access to checks.
Msg 5069, Level 16, State 1, Line 1
Alter Database statement failed.

Answer 4

Hi @Chris Youngblut ,

When database in SUSPECT state, we have only 3 choices.

(1) Do ALTER ONLINE again, order SQL Server to do a recovery again.
(2) Give up the current database and restore the database from a backup.
(3) Set the database status to EMERGENCY and try to repair the database.

> Error: 18456 : Severity: 14. State 38

State 38 means 'Login valid but database unavailable (or login not permissioned)'

Agree with Shashank, please using below T-SQL check your database state and your SQL login permission.

select user_access_desc, is_read_only, state_desc, is_in_standby  
from sys.databases where database_id = DB_ID('yourdb')  

select IS_SRVROLEMEMBER('sysadmin', name)  
from sys.server_principals  
where name = 'yourloginname'  

If your database is in suspect state, and your login has admin permission. You can set the database status to EMERGENCY and try to repair the database. Please refer to the blog How to Recover MS SQL Database from Suspect Mode to get more detail information.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

I will refer this thread to fix SUSPECT mode issue: https://stackoverflow.com/questions/19706601/all-databases-restoring-in-suspect-mode-in-sql-server-2012