May 5, 2015 at 11:04 am
We had a hardware issue on our server yesterday where the server admin was working on a failed drive in a mirror. This mirror is where the log files were stored. After he reconfigured the drive, all of our 30 plus dev db's are now in Recovery Pending status. I have tried every thing except third party software.
Does anyone have any thoughts on why this would have happened if anything can be done to remedy this issue.
The same server admin was supposed to be backing up the server and was not, so no db backups.
Thanks
May 5, 2015 at 11:59 am
Do the log files exist? edit: Is the log drive back online and accessible by the operating system? If so, are the log files there? If the log files are there, you can try setting each database offline and back online.
Now, if your log files are non-existent and you have no backups, you can set each database into emergency mode, and run a checkdb with repair_allow_data_loss to rebuild your log files.
See plenty of information here:
http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/
May 5, 2015 at 12:09 pm
Yes the log files exist.
May 5, 2015 at 12:17 pm
I edited my original post. If the log files exist, you can try to set each database offline and online again.
Also, start taking backups and check your production environment immediately for backups. If the do exist in production, restore one to development to ensure they actually work.
May 5, 2015 at 12:29 pm
I have taken the db offline then back online allowing data loss.
I have tried emergency, single_user mode and still get error messages.
In testing I have created a new db, then copied the files to it trying to "trick the server", but that didn't work either.
I was using the steps from this link, http://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-damaged-database/, by Paul Randal.
our production environment is mirrored servers with backups nightly and log backups every 15min.
Production is good. I just needed to follow up with my server admin better an not just take his word for it.
May 5, 2015 at 12:38 pm
What error messages are you getting? Are you unable to get the database into Emergency Mode,Single User mode?
The very last paragraph of the Randall article you linked states:
Note: it’s entirely possible that your database may be so damaged that not even EMERGENCY mode repair can fix it. If you cannot even get the database into EMERGENCY mode (e.g. if the database boot page, or the primary file’s file header page is damaged) then there is no way for SQL Server to access the database. At this point the only option is to restore from backups. If you don’t have backups, game over.
I personally would validate production environment if I were relying on the same server admin that is handling development....
May 5, 2015 at 12:59 pm
I know about the last paragraph, and am very worried.
here is the error when I place the db into emergency mode:Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:282; actual 0:0). It occurred during a read of page (1:282) in database ID 41 at offset 0x00000000234000 in file "my db". 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.
But the db goes into emergency mode.
This is what I get when I put it into single user mode:Msg 5011, Level 14, State 5, Line 2
User does not have permission to alter database 'my db', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Here again, the db goes into single user mode
When I run dbcc checkdb I get the following error:Msg 945, Level 14, State 2, Line 1
Database 'my db' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Here is the error I get when I try to put the db back online:Msg 5181, Level 16, State 5, Line 2
Could not restart database "my db". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Msg 9004, Level 21, State 1, Line 2
An error occurred while processing the log for database 'my db'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
The error logs show no more than the error message.
The backups on production are working correctly. I set them up and check the jobs daily for failures. I also place the backups on a different server.
May 6, 2015 at 3:16 am
Restore from a backup, I don't think that's fixable.
If you don't have a backup, script out what you can (with the DB in emergency mode), export what you can and recreate the DB.
The "incorrect pageid (expected 1:282; actual 0:0)." suggests that a section of the file has been zeroed out (misbehaving IO subsystem) and it's early enough in the file that the system tables are potentially affected. System table damage can't be repaired.
What's the full output of CheckDB (with the DB in Emergency mode). NOT with a repair option
DBCC CheckDB ('<database name>') WITH NO_INFOMSGS, ALL_ERRORMSGS
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
May 7, 2015 at 6:27 am
When I try to set the database into Emergency mode, I get the following message:Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:2531; actual 0:0). It occurred during a read of page (1:2531) in database ID 5 at offset 0x000000013c6000 in file 'E:\ReportServer.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.
But, when I check the state in sys.databases it shows being in emergency.
When I run dbcc checkdb I get the following error:Msg 945, Level 14, State 2, Line 1
Database 'my db' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
May 7, 2015 at 6:30 am
Then it's restore from a backup or complete loss of the DB. Sorry.
btw, this isn't damage to the log files, this is damage to the data files. Either the server admin was working on those drives too or something else went badly wrong.
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
May 7, 2015 at 6:42 am
Thanks for all of your help.
I will just take all of my production backups and restore them on development.
The good thing is we have multiple dev servers. The bad is we loose the data.
Another good lesson learned. Don't always trust that the server admin is always doing what he said he is doing.
I will be setting up my own db backups so this will not happen again.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply