October 28, 2016 at 5:40 am
Hello, our server hard disk crashed due to power failure and all the backups were on the hard disk. Before just before it did we had managed to copy the mdf file but power failed while copying the log file. The mdf can't be attached on a different machine now cos there was a transaction in progress when it was detached. So I created a new database, stopped the service and replaced the new mdf with the corrupt one and started the service again. Its state was Recovery_Pending. So I tried to run emergency mode repair using the following commands:
ALTER DATABASE HealthBookDB Set Single_User
GO
ALTER DATABASE HealthBookDB Set Emergency
GO
DBCC CHECKDB('HealthBookDB',REPAIR_ALLOW_DATA_LOSS)
GO
And the results were as follows:
Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file 'C:\HealthBookDB\HealthBookDB_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
DBCC results for 'HealthBookDB'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'HealthBookDB'.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:227761; actual 0:0). It occurred during a read of page (1:227761) in database ID 9 at offset 0x0000006f362000 in file 'C:\HealthBookDB\HealthBookDB.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:227761; actual 0:0). It occurred during a read of page (1:227761) in database ID 9 at offset 0x0000006f362000 in file 'C:\HealthBookDB\HealthBookDB.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:227761; actual 0:0). It occurred during a read of page (1:227761) in database ID 9 at offset 0x0000006f362000 in file 'C:\HealthBookDB\HealthBookDB.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.
I'm really at my wits end now can anyone help
October 28, 2016 at 6:04 am
Question, did you try to attach the mdf with ATTACH_REBUILD_LOG?
😎
October 28, 2016 at 6:31 am
When i run the following command:
CREATE DATABASE HEALTHBOOKDB ON (FILENAME = N'C:\HEALTHBOOKDB\HEALTHBOOKDB.mdf') FOR ATTACH_REBUILD_LOG
I get the following error:
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\HealthBookDB.LDF" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'HEALTHBOOKDB'. CREATE DATABASE is aborted.
October 28, 2016 at 7:19 am
The option FOR ATTACH_REBUILD_LOG will use the LDF-file if it exists on disk. If you really want to create the database with a new LDF file, you'll first need to remove or rename the existing LDF file.
Because the existing LDF file belongs to another database it can't be used with the MDF file you have copied.
October 28, 2016 at 7:48 am
I removed the existing ldf file when I run the 'CREATE DATABASE command and usually when I get the db into 'Recovery_Pending' state and run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS it removes any uncommitted transactions clears the existing log and creates a new one. So the issue is bigger than rebuilding the log. It can't rebuild the log file because of the circumstances under which the server was shutdown. There's an error msg in my first post:
'SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:227761; actual 0:0)'
I think that has to be resolved before the log can be rebuilt.
And sorry if I made duplicate posts I didn't know that wasn't allowed.
October 28, 2016 at 8:04 am
It isn't that duplicate posts aren't allowed, we just really frown on it as it tends to split responses across multiple threads and makes things a bit confused.
I just want to be sure about what I read in your initial post on this thread. All the backups were also on the same hard drive and were lost. You don't have copies of any of these backup files on another server or on tape.
If this is true, you may have lost all the data. I have read what others have posted and I don't have any other suggestions.
Only other thing I can suggest is to call Microsoft and open a case with them, they may be able to help but not making promises.
October 28, 2016 at 8:06 am
One other thing, if you have a twitter account, send a twitter with the url for this post and use the hashtag #sqlhelps and #databasecorruption.
October 28, 2016 at 9:44 am
Can you get the DB into Emergency mode at all?
ALTER DATABASE HealthBookDB Set Emergency
GO
If so, can you query any tables while it's in Emergency mode?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2016 at 1:43 pm
Hello, our server hard disk crashed due to power failure and all the backups were on the hard disk. Before just before it did we had managed to copy the mdf file but power failed while copying the log file. The mdf can't be attached on a different machine now cos there was a transaction in progress when it was detached. So I created a new database, stopped the service and replaced the new mdf with the corrupt one and started the service again. Its state was Recovery_Pending.
It is in these power failure situations that people realise the importance of a UPS (uninterruptible power supply). You were moving the MDF, LDF to somewhere else on the same disk ? Hmm, you may have a torn page if you say you had a transaction in progress when detaching the MDF (which leads me to wonder why you would do this). Really these then call for restoring from backup, but if your backups were on the same disk then that is a lost cause. I am surprised that a power failure ( I assume loss of electricity is meant here) would cause the needle on a hard drive to crash into the media. Usually the circuit breakers and power strips would keep the surges from happening but I am thinking outloud now.
Sounds like there needs to be a lot of governance rules set up at your place.
----------------------------------------------------
October 28, 2016 at 6:52 pm
MMartin1 (10/28/2016)
Hmm, you may have a torn page if you say you had a transaction in progress when detaching the MDF (which leads me to wonder why you would do this).
This would not result in data corruption. The transaction would have been rolled back before the detach completed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2016 at 5:14 am
I can get it into emergency mode but i can't query the tables.
When I run the following query:
Select State_Desc From Sys.Databases where Name='HealthBookDB'
I get the following result:
EMERGENCY
So I can get it into emergency mode but when I query a table eg:
Select * from Patients
I get the following error:
Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
October 29, 2016 at 5:22 am
When I query the Sys.Databases table the State_Desc column shows EMERGENCY for the database.
So I'm able to get it into emergency mode.
but when I run a query on a table eg:
Select * from Patients
I get the following error:
Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
October 29, 2016 at 11:10 am
Then I'm sorry to say it's probably toast and not recoverable. Emergency mode repair's a last resort and has never been guaranteed to work.
Check the MSDB backup tables, maybe you'll find a not-too-old backup that was taken to another location, maybe there's a copy in dev or on a share or similar.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2016 at 11:13 am
Lynn Pettis (10/28/2016)
Only other thing I can suggest is to call Microsoft and open a case with them, they may be able to help but not making promises.
Though MS support will not do data recovery, or assist past the supported recovery methods in SQL (CheckDB and restore from backup), at least that's how it was the last time I heard.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2016 at 1:40 pm
Looks FUBAR, questions, how big is the database? what is the latest good backup? what other recovery options are there (manual data entry etc.)? do you have the failed hard drives?
😎
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply