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 :

http://msdn.microsoft.com/fr-fr/library/bb522682.aspx

For more information about DBCC CHECKDB :

http://msdn.microsoft.com/fr-fr/library/ms176064(v=sql.100).aspx

Finally, in order to avoid these case, don’t forget power redundancy or even more secure :