SQL Server Error 823 Troubleshooting and Resolution
SQL Server Error 823
If you are getting this error, chances are you are having a hardware failure on your server, or perhaps someone deleted one of the database files.
Assuming you took care of that situation:
Do not Detach the Database
The very easy fix to do is to simply restore the database using your backups. But if you don’t have a good set of backups, Paul Randal ( he and his wife Kim, are SQL GODS) has a great set of steps to try in his article here.
Here is the short story of how to get your database up and running if your transaction log was damaged.
Error 823 Resolution
Switch the database into the emergency/single-user mode:
alter database <dbname> set emergency;
alter database <dbname> set single_user;
dbcc checkdb(‘<dbname>’,REPAIR_ALLOW_DATA_LOSS) with all_errormsgs, no_infomsgs;
alter database <dbname> set multi_user;
alter database <dbname> set online;
The main thing to realize here is that if the transaction log is damaged or missing that the transactions are not going to be found in the transaction log. You are going to lose any active transactions that have not been commited to the database. The real area of interest is the REPAIR_ALLOW_DATA_LOSS. This particular arguement allows you to recreate the transaction log. Do not take this command lightly as it will lose data, so please use it only as a last resort.