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;
go
alter database <dbname> set single_user;
go
dbcc checkdb(‘<dbname>’,REPAIR_ALLOW_DATA_LOSS) with all_errormsgs, no_infomsgs;
go
alter database <dbname> set multi_user;
go
alter database <dbname> set online;
go

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.

 

Advertisements

One thought on “SQL Server Error 823 Troubleshooting and Resolution

Have an opinion? Leave an opinion!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s