Problem: The disk with transaction log crashed. Database have status suspect.- Database cannot be opened because some of the files could not be activated

Problem: The disk with transaction log crashed. Database have status suspect. Detach was succeed, but attach_single_file_db is not. i.e. attempt for attaching Database (without log) return this error:

Server: Msg 945, Level 14, State 2, Line 1
Database 'test1' cannot be opened because some of the files could not be activated.
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'test1'. CREATE DATABASE is aborted.

Creating new database and substitution of garbled file is not useful.

Solution:



1. Create new database with same name and same by name and same placement of .mdf and .ldf files
2. Stop server, replace .mdf file
3. Start server, but do not pay attention for database status.
4. From Query Analyzer (QA) run script
Use master
go
sp_configure 'allow updates', 1
reconfigure with override
go

4a. In the same place run
select status from sysdatabases where name = '<db_name>'
and save value for accident failure

5. In the same place run
update sysdatabases set status= 32768 where name = '<db_name>'

6. Restart SQL Server

7. Database should be seen (in emergency mode).

8. From QA (Query Analyzer) run:
DBCC REBUILD_LOG('<db_name>', '<name of new log file with full path>')

SQL Server will say - Warning: The log for database '<db_name>' has been rebuilt.

9. If all ok, in the same place run
Use master
go
sp_dboption '<db_name>', 'single_user', 'true'
go
USE <db_name>
GO
DBCC CHECKDB('<db_name>', REPAIR_ALLOW_DATA_LOSS)
go

9a.
If you attempt for moving database in single user mode is not successful, then for cheking data you could try
dbo only mode
sp_dboption '<db_name>', 'dbo use only', 'false'

10. If all ok:
sp_dboption '<db_name>', 'single_user', 'false'
go
Use master
go
sp_configure 'allow updates', 0
go


Translated by Nashol.biz from:
http://www.sql.ru/faq/faq_topic.aspx?fid=123





Теги: crash :: crashed :: SQL :: 2000 :: Server :: MS SQL Server 2000 :: transaction log :: problem :: solving :: MS :: Microsoft :: DB :: Database :: log :: log file :: suspect :: status :: transaction :: solution :: repair :: select :: update :: create :: mdf :: ldf :: SQL Server :: checkdb :: dbcc :: Msg 945 :: 945 :: Level 14 :: State 2 :: 14 :: Msg 1813 :: Level 16 :: 1813 :: 16