How to Repair the SBSMonitoring Database

My daily report from SBS 2008 told me that MSSQL$SBSMONITORING had raised error event 8646 “Unable to find index entry in index ID 6, of table 357576312, in database ‘SBSMonitoring’. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE.”

Run as Administrator

I connected to the SBSMonitoring SQL instance with SQL Server Management Studio Express using Windows Authentication (I’m logged in as the SBS admin). But when I tried to connect to the SBSMonitoring database, I got the message, “The database SBSMonitoring is not accessible. (Microsoft.SqlServer.Express.ObjectExplorer)”

Susan Bradley suggested that I Run as Administrator. Sure enough, once I started SQL Server Management Studio Express as Administrator, I was able to connect to and manage the SBSMonitoring database.

DBCC CHECKDB

When I ran DBCC CHECKDB, I got lots of errors like this:

DBCC results for ‘WMICollectedData’.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘WMICollectedData’ (ID 357576312). Data row does not have a matching index row in the index ‘_SBS_BLOG_index_WMICollectedData_5_K4_K1_K5_2’ (ID 6). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:4718:18) identified by (ID = 421087) with index values ‘WMIPropertyID = 100 and ID = 421087 and WMIInstanceID = 62’.

Check and Repair

So I ran this query:

alter database sbsmonitoring set SINGLE_USER
DBCC CHECKDB (sbsmonitoring, repair_rebuild)
alter database sbsmonitoring set MULTI_USER

which inserted several index entries and repaired several errors:

Repair:  Successfully inserted row in index “dbo.WMICollectedData, _SBS_BLOG_index_WMICollectedData_5_K4_K1_K5_2” in database “SBSMonitoring”.
Msg 8951, Level 16, State 1, Line 2
Table error: table ‘WMICollectedData’ (ID 357576312). Data row does not have a matching index row in the index ‘_SBS_BLOG_index_WMICollectedData_5_K4_K1_K5_2’ (ID 6). Possible missing or invalid keys for the index row matching:
        The error has been repaired.
Msg 8955, Level 16, State 1, Line 2
Data row (1:4718:18) identified by (ID = 421087) with index values ‘WMIPropertyID = 100 and ID = 421087 and WMIInstanceID = 62’.

Check Again

Finally I ran DBCC CHECKDB by itself again; no errors found!