October 22, 2007 at 9:24 am
Hello everyone,
Since I noticed a certain former MS employee posting in the data corruption section, I figured I would look for some input. 😉 The disaster has since been recovered, but I wanted to see if there was something I missed (an alternate data recovery path, for example). Here is the situation:
Last week, I encountered a SQL instance that had a database marked in suspect mode. I researched the problem in the SQL logs and got the following error:
"During undo of a logged operation in database 'CCDMA_AA2612834', an error occurred at log record ID (10851:13457:4). The row was not found. Restore the database from a full backup, or repair the database."
I set the database into emergency mode and then detached it, hoping I could reattach with the option to create a new log file (some transactions lost, but less than if recovered from a backup). Essentially I got an error on attach about the database not being in a proper mode to reattach. Is there something I missed that I could have done otherwise? At any rate, I restored from backup with some data loss. At any rate what do you guys think (Paul)?
Thanks,
Eric
October 22, 2007 at 1:00 pm
Hi Eric,
I'm guessing that you detached, deleted the log file and then tried to re-attach? SQL Server won't allow you to attach a database without a log file if the database wasn't cleanly shutdown (i.e. there's no recovery to run).
Your best bet in the situation you describe (I'd always recommend using backups first) would be to run emergency mode repair to create the new transaction log for you. Here are a couple of blog posts on this topic you may find interesting:
- Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database
- CHECKDB From Every Angle: EMERGENCY mode repair - the very, very last resort
Hope this helps
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 22, 2007 at 4:37 pm
Thank you, Paul. This is exactly what I'm looking for. I did pretty much what you surmised, but I wanted to see what my other options were, had I needed to use them.
Eric
October 29, 2007 at 5:48 am
dear Eric
i have one techenique for you just apply may be your problems will resolve.
i am sure that you still have LDF and MDF file in your hard drive.
just rigt click on you LDF file and mark on read only option
inshallah your database will attach and come back on your last mode
just do it and reply as soon as possible
Regards
Syed Muhammad Naveed
Database Administrator
October 29, 2007 at 8:59 am
syed muhammad naveed (10/29/2007)
dear Erici have one techenique for you just apply may be your problems will resolve.
i am sure that you still have LDF and MDF file in your hard drive.
just rigt click on you LDF file and mark on read only option
inshallah your database will attach and come back on your last mode
just do it and reply as soon as possible
Regards
Syed Muhammad Naveed
Database Administrator
Can you explain your rationale here?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 29, 2007 at 12:06 pm
Can't say that I've tried that one before, but the issue has long since been resloved, if you check my 1st post. I was doing a post-mortem analysis, since I had the luxury of time after the fact. As for your post, can you explain what you are talking about? I think you've even stumped the MS engineer who has worked on that part of the DB engine! 😉
THanks,
Eric
October 30, 2007 at 2:01 am
actually i m sharing you my personal experience you know the database is totally depend on the operating system if the problem occur in OS then database will not enter you because the address of data come from winds clustered data dictionary and registery as well.
just right click on ur LDF file go in win properties mark the database read only and atached the database through enterprise manager.
check and confirmed.
Regards
Syed Muhamad Naveed
Database Administrator
October 30, 2007 at 2:10 am
if you have deleted your log file LDF dont worry
through this software you can get back your LDF file
1) file Scevenger
2) Get data back
when you get LDF file you may apply this things
Regards
Syed Muhammad Naeed
Database Administrator
October 30, 2007 at 12:48 pm
syed muhammad naveed (10/30/2007)
actually i m sharing you my personal experience you know the database is totally depend on the operating system if the problem occur in OS then database will not enter you because the address of data come from winds clustered data dictionary and registery as well.just right click on ur LDF file go in win properties mark the database read only and atached the database through enterprise manager.
check and confirmed.
Regards
Syed Muhamad Naveed
Database Administrator
What utter nonsense. The log file of an uncleanly shutdown database has to be opened read-write - you'll get an access denied error in the errorlog and the database won't startup. I just tried it on 2005.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply