Fix MS SQL Error 823 I/O Error
Definitions:
- this should be replaced, in any query detailed in the steps
below, with the database being recovered.
- this should be replaced with the path to the SQL data
files.
- this should be replaced with the filename of the SQL
database log file minus the extenstion.
1. In SQL Enterprise Manager, create a new database with the same name as
the old one. Make it the same data file size or larger as the old MDF.
2. Stop SQL Server.
3. Delete the new MDF file and rename the old one back.
4. Start SQL Server. The database should now show up tagged Suspect in
Enterprise Manager.
5. In SQL Query Analyser:
use master
go
sp_configure 'allow', 1
go
reconfigure with override
go
update sysdatabases set status=32768 where name=''
go
dbcc rebuild_log('','\logfile>1.ldf')
go
NOTE: the second to last command is not a typo. The last variable of
1.ldf is correct. If your database was named CONTACTS this
would give you CONTACTS_LOG1.ldf. Don't forget the '1' at the end.
This should result in a message similar to, "Transaction log successfully
rebuilt - transactional consistency lost."
6. In SQL Query Analyzer:
dbcc checkdb('')
This may result in a number of errors. If so, go to step 7. If there are no
errors, the process may be complete. Check step 9.
7. In SQL Query Analyzer:
dbcc checkdb('',repair_allow_data_loss)
You may have to set the database to single user for this to work. It will
alert you to this. This can be done in Enterprise Manager under the
databases properties.
8. Repeat steps 9 and 10 until step 9 results in no errors.
9. The database may end up with a status of "dbo use only". To clear this,
execute the following in SQL Query Analyzer:
use master
go
exec sp_dboption '', 'dbo use only', 'false'
go
You can also do this last step in Enterprise Manager under the databases
properties.
below, with the database being recovered.
files.
database log file minus the extenstion.
1. In SQL Enterprise Manager, create a new database with the same name as
the old one. Make it the same data file size or larger as the old MDF.
2. Stop SQL Server.
3. Delete the new MDF file and rename the old one back.
4. Start SQL Server. The database should now show up tagged Suspect in
Enterprise Manager.
5. In SQL Query Analyser:
use master
go
sp_configure 'allow', 1
go
reconfigure with override
go
update sysdatabases set status=32768 where name='
go
dbcc rebuild_log('
go
NOTE: the second to last command is not a typo. The last variable of
would give you CONTACTS_LOG1.ldf. Don't forget the '1' at the end.
This should result in a message similar to, "Transaction log successfully
rebuilt - transactional consistency lost."
6. In SQL Query Analyzer:
dbcc checkdb('
This may result in a number of errors. If so, go to step 7. If there are no
errors, the process may be complete. Check step 9.
7. In SQL Query Analyzer:
dbcc checkdb('
You may have to set the database to single user for this to work. It will
alert you to this. This can be done in Enterprise Manager under the
databases properties.
8. Repeat steps 9 and 10 until step 9 results in no errors.
9. The database may end up with a status of "dbo use only". To clear this,
execute the following in SQL Query Analyzer:
use master
go
exec sp_dboption '
go
You can also do this last step in Enterprise Manager under the databases
properties.
ความคิดเห็น