Suspect vCenter database on SQL Express
We met a quite funny/annoying behavior on a SQL Express in a homelab.
We were doing lots of testing during stormy evening in Paris, and of course, general power failure right occured during a test (otherwise there’s no fun ^ ^).
Result : servers reboot, keeping a tight ass during the 8 To RAID-5 check consistency (waiting for nexenta migration !) and just check what’s down :p First bad news, no more vCenter, after closer look, the vCenter service crashed about database connectivity.
We connect to the SQL server, and we found this :
At least, we understand now why the vCenter service crashed ^^ SQL Server will not let any operation on suspect database until it’ll be repaired ! In order to have more information on why the database is Suspect, you just need to execute the following SQL command :
DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS
For our problem, we knew the cause :p but this command can be usefull in other case.
In order to repair the database, we need to run some SQL command :
EXEC sp_resetstatus 'VIM_VCDB' ALTER DATABASE VIM_VCDB SET EMERGENCY ALTER DATABASE VIM_VCDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
The stored procedure sp_resetstatus turns off the suspect flag on a database. This procedure updates the mode and status columns of the named database in sys.databases
The EMERGENCY mode mark the database READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role.
The SINGLE_USER specifies that only one user at a time can access the database (mandatory for repair with DBCC CheckDB)
DBCC CheckDB ('VIM_VCDB', REPAIR_ALLOW_DATA_LOSS)
This command will try to repair errors. Theses repair could cause data loss.
ALTER DATABASE VIM_VCDB SET MULTI_USER
We put database in production state. It has no longer the Suspect flag and can be accessed as usual.
For more information about ALTER DATABASE :
For more information about DBCC CHECKDB :
Finally, in order to avoid these case, don’t forget power redundancy or even more secure :