So if you have been following along, I am currently at the BizTalk RFID Solution Days Conference and Training. Yesturday I noticed that some of my databases on my training VM were marked as suspect and the BizTalk services such as SSOP and the actual BizTalk service would not start. Upon further investigation in the event log, I was getting alot of errors indicating a corrupt msdb database.

Now the easy way would have been to ask for a new VM hard drive image, but this was too good of a learning opportunity to pass up. Better to figure out how to fix this in a low stress environment then on my production environment at 3am.

My first step was to fix the msdb database. I found this article on how to rebuild your msdb database from a script. The script is called instmsdb.sql, and you can run it if you have or do not have a msdb database defined. In my case my database was defined so it just rebuilt it.

That fixed all of the corrupt index errors for msdb in the event log and was left with the SSO service failing to start. Here is another case of where you need to read more then the first event log message. I thought the issue was with the SSO DB, but in fact, it was with the BizTalkDb database (being suspect). Once I isolated the actual problem, I could move on to repairing the database.

I found several posts on different work around's for fixing a suspect database, but this one, was the one that finally worked. In essence, you run the following sql script:

   1:  EXEC sp_resetstatus 'yourDBname';

   2:  ALTER DATABASE yourDBname SET EMERGENCY

   3:  DBCC checkdb('yourDBname')

   4:  ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

   5:  DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)

   6:  ALTER DATABASE yourDBname SET MULTI_USER

There were some errors displayed when fixing both the BizTalk and BamAlerts database, but in the end, everything is working now.