HELP me please ....
-
I'm running sql 2005 (9.0.3054) and one of the drives that one of our databases was on suddenly powered off. Now that the drive is powered back on I can see the database but I can't view the properties of it or just about anything else. I keep seeing this error;
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:823
Date:1/19/2010
Time:1:09:33 PM
User:N/A
Computer:DC-SV-SQL1
Description:
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000457e96000 in file 'K:\databases\HITSNS_Message_Journal_Test.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level 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.
I could not run any checkdb commands either. Oddly enough the tables also seem to be completely screwed up - there are tables missing and those that are there look to be from other databases (?)....
Any advice that will totally fix this mess? Rebooting right now isn't really an option as other databases are hosted on this instance.
-
First, and most importantly, do you have current backups? Was the log file on the same drive? If not, you may want to do a tail log backup. After powering on the drive was SQL Server restarted?
-
Yes I have a backup - the log is on a different drive. Do you think the only option is to start restoring? SQL was running the entire time the drive was unavailable.
-
I think you must restore the latest backup. If you were using Full recovery and you still have the backup files, then you can try what Lynn advices you.
-
Best suggestion, tail-log backup, then restore the database.
-
Thanks everyone for the reply's, I do wish there was another option though - good to know what to do but the recovery will take some time ...
Thanks!
-
Another related question - how can I remove the bad database now? Keep getting os 21 error when I try and delete the database....
-
Yes I have a backup
Question is... which recovery model is in place?
Is it Full or Bulk-logged?... in the affirmative case Lynn's suggestion e.g. taking a tail-log backup - would allow to recover until point of fairlure instead of until point of last log backup.
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
scott williams-465021 (1/19/2010)
I could not run any checkdb commands either. Oddly enough the tables also seem to be completely screwed up - there are tables missing and those that are there look to be from other databases (?)....It seems to be the physical file is correupted.....
Is there any specific reason you can't run checkdb??
-
Following on with PaulB, was the database using FULL or BULK_LOGGED model?
If so, were you able to complete a tail-log backup?
If so, you may need to try 1) Restarting SQL Server or 2) Rebooting the server. You should still, probably, restore the database. I'd be very concerned about any possible data corruption.
-
When I run checkdb I get the same error above.
" The operating system returned error 21 (The device is not ready.) to sql server during a read at offset ....."
I'll have to restore the last full backup and thats it, losing todays data is tolerable - this database houses logs and its more for reporting purposes. Should I be able to just delete the database though management studio?
-
scott williams-465021 (1/19/2010)
When I run checkdb I get the same error above." The operating system returned error 21 (The device is not ready.) to sql server during a read at offset ....."
I'll have to restore the last full backup and thats it, losing todays data is tolerable - this database houses logs and its more for reporting purposes. Should I be able to just delete the database though management studio?
Should be able to, but you may have to 1) restart SQL Server or 2) Reboot the server.
-
Thanks again... I'll post a follow up later.
-
I'd suggest a reboot. Once SQL's back online, run a checkDB on the original database, see if everything's OK.
You probably didn't need to restore the database, very likely once the OS restarts it will see the drive properly and SQL will be able to access the database again. As long as the transaction log wasn't damaged and there wasn't any damage done to the data file due to the disconnect (like a torn page) the DB should be intact and usable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I'd suggest a reboot/restart as well. We lost connectivity to the SAN during a network upgrade and the database server wasn't shutdown first. The drive came back ok when the network came up, but SQL Server didn't 'reconnect' to the file. Got the same error (error 21, device not ready), but rebooting the box brought it back and luckly the CheckDB came back clean. I'm thinking a service restart might have fixed it too. My first clue was I was able to rename the .mdf even though the database appeared to be attached in SSMS.
Good Luck!
Chad
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply