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 :

6 comments

  1. Thank you so much for this link. We first stopped the SQL services and manually copied the databases. Then we restarted the SQL services and performed the tasks you listed. Inside the query window we saw the results which included repair status. Upon completion we saw the Suspect flag disappear, so we rebooted the server and voila’ vmware was back online! Thanks so much!!

  2. Pingback: Podłożyć komuś świnię | THX 1138

  3. Pingback: Ciąg dalszy problemów z vCenter Server - baza danych w trybie (suspect) - www.karolszczepanowski.pl

Leave a Reply

Required fields are marked *.