August 4, 2010 at 8:00 pm
I was doing an update on millions of records without any batches and cancelled it as it was taking too long and it was rolling back, during this process there was a disk outage and database went into recovery mode. I have cleared some space in the disk and now i dont see database in recovery mode but i see this message in the logs.
Message
During undoing of a logged operation in database , an error occurred at log record ID (5147:166612:162). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
What would be the best option for me noe, i also dont have backup of the database.
August 4, 2010 at 8:23 pm
Ouch. Here are my questions:
1. Have you checked the windows logs for the specific failure mentioned? If so, can you post it?
2. Can you connect to the database?
2. If you can connect to the database run DBCC CHECKDB('your database name') with ALL_ERRORMSGS and post the results here.
I've never dealt with this situation, but, depending on what CHECKDB returns, I think you are going to be in trouble without a backup.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 4, 2010 at 11:54 pm
Restore from backup.
Oh, wait, no backup. Why the hell not?
Run the following, post the results, hope that the data in that database is not critical.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
If the DB is suspect (very possible), you'll have to set it to Emergency mode first.
ALTER DATABASE <Database Name> SET EMERGENCY
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
August 5, 2010 at 12:00 am
You don't have a backup of the database, but do you have a copy of it anywhere? For instance did you copy it to any other server for testing or development purposes?
Whenever performing massive operations like this, I like to have a backup of the database right before I run the changes.
Besides that backup there should be routine daily backups of the db - especially if it is critical.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2010 at 12:02 am
CirquedeSQLeil (8/5/2010)
I like to have a backup of the database right before I run the changes.
Me too, but some people seem to want to live dangerously.
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
August 5, 2010 at 12:10 am
GilaMonster (8/5/2010)
CirquedeSQLeil (8/5/2010)
I like to have a backup of the database right before I run the changes.Me too, but some people seem to want to live dangerously.
That's the kind of danger that causes long nights and weekend work (best case scenario). I prefer not to live dangerously in that fashion.
It is something though that should / could be learned from after the first time (hopefully it doesn't happen again).
It also never fails that when you think the change is insignificant and decide to skip the backup part - something bad is going to happen.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2010 at 12:17 am
CirquedeSQLeil (8/5/2010)
It also never fails that when you think the change is insignificant and decide to skip the backup part - something bad is going to happen.
Wasn't the update that cause this. Likely cause is the mentioned 'disk outage'. Something corrupted either data or log file (can't tell which)
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
August 5, 2010 at 12:23 am
GilaMonster (8/5/2010)
CirquedeSQLeil (8/5/2010)
It also never fails that when you think the change is insignificant and decide to skip the backup part - something bad is going to happen.
Wasn't the update that cause this. Likely cause is the mentioned 'disk outage'. Something corrupted either data or log file (can't tell which)
Agreed. The change was just an update. A rollback was started but then a disk outage occurred. Disk outage would have possibly killed the DB regardless of the change. I would call the change insignificant but would have done a backup anyway. In this case something bad happened - though seemingly unrelated to the change.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply