Today morning I observed that one of our logshipped secondary database reached into Suspect mode, I just investigated and found that log file has been corrupted That’s why i decided to share this incident to our other DBA & developers help.
But there are many reason to enter a database in suspect Mode:–
- Log files(.ldf) or data files(.mdf) Corruption
- Due to lack of disk space
- Due to improper shutdown of Sql Server, you mostly find SQL Server database in suspect mode
- Unable to complete rollback operation or roll forward operation
These are the below steps to recover database from suspect mode :-
- To remove the suspect mode we have to set database to Emergency Mode.
(Emergency Mode provides the ability to access the database and fix them in case of any data loss disaster. )
To Set the database in Emergency Mode, run the following SQL query:
ALTER DATABASE db_name SET EMERGENCY
- Once you have the access of database, execute a Consistency Check on master file. DBCC function are able to find out all the logical as well as physical fault within the database.
DBCC CHECKDB (‘Db_Name’)
- Now, Take database to Single User Mode and roll back to last incomplete transaction with the help of following command:
ALTER DATABASE database_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- If errors are listed during the DBCC check, then execute the following query:
DBCC CheckDB (‘db_Name’, REPAIR_ALLOW_DATA_LOSS)
- At last, switch database from Single user mode to Multi User Mode and check the database accessibility.
ALTER DATABASE db_Name SET MULTI_USER
Hope it will help you out guys!!!
woh I like your articles, bookmarked! .