The case of a corrupt AdventureWorks database in SQL Server

I usually keep a copy of the AdventureWorks database handy in case I quickly need to test some SQL language query or operation with suitable testing data. Of course, AdventureWorks cannot match every possible case, but it's still handy and already has enough testing data in place.

Recently, one of my testing computers started complaining that my AdventureWorks database was in recovery mode. Sure enough, some kind of corruption had occurred at some point (power loss/blue screen?) but this time it seemed bad: DBCC CHECKDB didn't work, and every time I tried running any SQL commands against the database, the SQL Server engine's service refused to stop, and just stayed running to no tangible results.

So, the usual repair operations like single user mode didn't help. But since AdventureWorks is a sample database, no problem, I can just delete it. But the corruption was severe enough to cause SQL Server to stall: it wouldn't even let me delete the database using SQL Server Management Studio (SSMS).

The solution: go to Windows Services, find the SQL Server engine service, set startup more to "Manual", and reboot the computer. Next, physically delete the AdventureWorks files (both .mdf and .ldf) and then restart the engine service (reverting back to Automatic startup). Of course, the database shows as unavailable in SSMS, but now you can delete it.

Then, locate the AdventureWorks OLTP SQL script, and run it. You have a pristine database again!