Fix the database state in MSSQL

Published on: 14th Sep 2019

Updated on: 19th May 2021

Overview

What the hell on earth that the database state became "recovery" after rebooted the server? Waiting and waiting and rebooting does not change the database state back to the normal state.

Solution

If this has happened, we need to fix this issue manually.

To view the current database state:

select name, state_desc 
from sys.databases

To fix the problematic database:

alter database test set emergency;
go

alter database test set single_user
go

dbcc checkdb (test, repair_allow_data_loss) with all_errormsgs;
go

alter database test set multi_user
go

Warning: before running the above commands, please make sure you have done sufficient research on the Internet before executing it.

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.