The day before yesterday the power flickered at our house and because I had procrastinated installing a UPS in my home lab, my systems all powered off unexpectedly. Yesterday I went out and bought the UPS and powered everything back on but my vCenter instance was throwing SQL errors all over the place in the event logs. I looked for a quick “how to” for repairing the vCenter SQL Express DB but was unable to find anything specific to vCenter. Here is what I did.
- Please remember, this is only a lab and you should always call support or talk to your DBA before doing something like this in production. This specific SQL query can loose data in your vCenter database!
1) Install SQL Server Management Studio. Just search Google for it but make sure to download the same version as your SQL Express instance.
2) Connect to the SQL Server instance. The instance you connect to is “.\VIM_SQLEXP” without the quotes.
3) You will probably see something like the following, note the VIM_VCDB is marked as suspect.
4) Select the VIM_VCDB database.
5) Paste the following into the Query area and click execute:
EXEC sp_resetstatus VIM_VCDB;
ALTER DATABASE VIM_VCDB SET EMERGENCY
DBCC checkdb(VIM_VCDB)
ALTER DATABASE VIM_VCDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (VIM_VCDB, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE VIM_VCDB SET MULTI_USER
6) Wait. This make take a while, it took about 45 seconds on my system.
7) Refresh the view and you should see the following. Note that the “Suspect” status is gone.
8) Restart vCenter or just reboot the host if everything is running on the same box/vm.