March 13, 2008 at 4:24 pm
yes paul;, realised that as soon as I posted, which is why I deleted post, but you were too quick for me! :blush:
---------------------------------------------------------------------
October 28, 2008 at 8:29 pm
Hi! I'm just new here and I'm not really sure if I should have created a new thread but my problem's the same so I thought of just replying here. 🙂
Here's the story:
our development database just became suspect and here are the logs:
2008-10-29 08:43:00.71 spid16Database 'BWMIS' (database ID 9) could not recover. Contact Technical Support..
2008-10-29 08:43:00.71 spid16Error: 3414, Severity: 21, State: 1
2008-10-29 08:43:00.68 spid16Error while undoing logged operation in database 'BWMIS'. Error at log record ID (29654:88890:124)..
2008-10-29 08:43:00.68 spid16Error: 3314, Severity: 21, State: 3
2008-10-29 08:43:00.68 spid16Could not find the index entry for RID '3600000000429b000006000005001e0024002e0
2008-10-29 08:43:00.68 spid16Error: 644, Severity: 21, State: 1
2008-10-29 08:43:00.56 spid16Recovery of database 'BWMIS' (9) is 1% complete (approximately 541 more seconds
2008-10-29 08:43:00.28 spid16Recovery of database 'BWMIS' (9) is 1% complete (approximately 541 more seconds
2008-10-29 08:42:47.84 spid16Recovery of database 'BWMIS' (9) is 1% complete (approximately 543 more seconds
2008-10-29 08:42:42.15 spid16Recovery of database 'BWMIS' (9) is 0% complete (approximately 623 more seconds
2008-10-29 08:42:42.15 spid16Analysis of database 'BWMIS' (9) is 100% complete (approximately 0 more seconds
2008-10-29 08:42:38.42 spid16Analysis of database 'BWMIS' (9) is 14% complete (approximately 2 more seconds)
2008-10-29 08:42:37.85 spid16Starting up database 'BWMIS'.
2008-10-29 08:42:36.71 spid62Error while undoing logged operation in database 'BWMIS'. Error at log record I
2008-10-29 08:42:36.71 spid62Error: 3314, Severity: 21, State: 5
2008-10-29 08:42:36.71 spid62The log for database 'BWMIS' is not available..
2008-10-29 08:42:36.71 spid62Error: 9001, Severity: 21, State: 1
2008-10-29 08:42:36.70 spid62Error while undoing logged operation in database 'BWMIS'. Error at log record I
2008-10-29 08:42:36.70 spid62Error: 3314, Severity: 21, State: 4
2008-10-29 08:42:36.70 spid62Could not find the index entry for RID '3600000000429b000006000005001e0024002e003a003e003000300030003100380031003900310031003000310031003400' in index page (1:1800917), index ID 0, database 'BWMIS'..
2008-10-29 08:42:36.70 spid62Error: 644, Severity: 21, State: 1
2008-10-29 08:42:36.65 spid62Error while undoing logged operation in database 'BWMIS'. Error at log record ID (29654:5045:1)..
2008-10-29 08:42:36.65 spid62Error: 3314, Severity: 21, State: 4
2008-10-29 08:42:36.60 spid62Could not find the index entry for RID '3600000000429b000006000005001e0024002e003a003e003000300030003100380031003900310031003000310031003400' in index page (1:1800917), index ID 0, database 'BWMIS'..
2008-10-29 08:42:36.60 spid62Error: 644, Severity: 21, State: 1
2008-10-29 08:42:15.65 spid62A possible database consistency problem has been detected on database 'BWMIS'.
2008-10-29 08:42:15.65 spid62Error: 7987, Severity: 22, State: 2
2008-10-29 08:42:15.62 spid62Stack Signature for the dump is 0xE70FF417
2008-10-29 08:42:03.10 spid62Using 'dbghelp.dll' version '4.0.5'...
2008-10-29 08:42:02.98 spid62ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to cre
We detached the database and tried re-attaching it but to no avail. Message "error 644: Could not find the index entry for RID....." was displayed.
We have no database backup so we can't perform restoration.
Hope somebody can help me asap and thanks in advance! 🙂
October 28, 2008 at 9:36 pm
Hi,
Welcome to SQLServerCentral! Usually creating a new thread is the way to go, but no big deal.
Well, unfortunately you've done the worst thing you could do by detaching the database. When a database is attached, recovery has to complete otherwise the attach fails. A suspect database is one where recovery could not complete - so detaching it isn't a good thing to do. You can hack the database back into the server and then run repair on it. Check out this blog post link which details the procedure for doing this. Let us know how you get on.
Cheers
PS Get a backup strategy that allows you to recover from problems like this!
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, 2008 at 12:56 am
Hi Paul,
Thanks for the immediate reply. 🙂
Yes, that was a bad mistake indeed detaching the database. I'm going to learn from this and not do the same mistake again. Anyway, I'll read first the link you gave and give it a try. Will post again.
Thanks!
October 29, 2008 at 1:50 am
Hi Paul,
I created a new database with the same file layout (1 mdf & 1 ldf). However, I used only the default sizes for the dummy database. The suspect DB's MDF file is 64+GB while the log file's 1+GB. Is that okay? Or should I have set the new database's mdf & ldf file to the same size?
I then took the new db offline so I could rename the files (I read this in another website before I read your post) and didn't shut down SQL Server. And when I tried refreshing the sql server, it sort of hung. So we restarted the server and opened Enterprise Manager again. The dummy database became suspect/offline.
When I ran this statement, the output is OFFLINE.
SELECT DATABASEPROPERTYEX ('BWMIS', 'STATUS') AS 'Status';
What should I do next? When I tried bringing it online, it hung and I had to restart the server again.
PS: We basically let the development team manage their dev databases since it's not yet in production. However, they don't have a backup of this database due to limited disk space.
Also, FYI: we're using SQLServer 2000 (I'm sorry, I didn't notice that I was in the SQL Server 2005 part of the forum. 🙁 )
October 29, 2008 at 11:11 am
ok - you need to statr the process again and follow the steps exactly - i.e. same size files etc. I haven't tested this using different steps so I can't troubleshoot something I haven't tried.
Using 2000, you're not going to be able to use the second half of the fix which uses emergency mode and log file rebuilding with documented and supported commands. You're going to have to manually hack the status to emergency mode and use DBCC REBUILDLOG, then run a full DBCC CHECKDB with repair. If you search around you'll find instructions on how to do that - I don't have anything on my blog that details all the steps for 2000 (and don't have bandwidth to type them all in with all the caveats I'm afraid).
Thanks
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 31, 2008 at 2:38 am
Hi Paul,
Was able to put the database to emergency mode. Now the database's status in EM is Read-Only/Offline/Emergency Mode. I've already created another database and used DTS to re-create the objects and transferred the data from the old db to the new db. However, I'd like to know if there's still a way to bring back the old db to normal?
Thanks a lot. 🙂
October 31, 2008 at 2:50 am
Well, not back to normal. You can try DBCC REBUILD_LOG and then DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS - that might make the database be usable again but some data will be lost.
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
November 3, 2008 at 6:03 pm
Hi Paul,
I tried using DBCC CheckDB ('BWMIS', REPAIR_ALLOW_DATA_LOSS), but didn't work.
I've already detached this database and asked the dev team to use the new db instead.
Anyway, thanks a lot for your help! 🙂 Next time I encounter a suspect DB, I won't detach it anymore. 🙂
March 20, 2009 at 10:01 am
Hi beninimus (and Paul)
How did you connect to the corrupted database in DTS? I have a database that is in Suspect/Emergency mode, but cannot connect in DTS to move the good tables over to a previous backup.
I have tried DBCC CHECKDB which gives the error:
Could not read and latch page (26880:1946183936) with latch type SH. sysobjects failed.
Is is possible to retrieve data from a database that's giving this level of error?
regards
Daniel
March 20, 2009 at 1:40 pm
In future please start a new thread for a new problem.
Take a look at this article. http://www.sqlservercentral.com/articles/65804/ What you've got there is one of the irreparable errors. CheckDB can't read sysobjects (SQL 2000 database? This thread is in the 2005 corruption forum) and hence can't work out what the database is supposed to look like.
I hope you have a backup. If you do, restore it, checkDB is not going to run and hence repair isn't possible.
If not, please start a new thread in the SQL 2000 data corruption forum and give as much info as possible on the problem, including what you've done so far and any entries in the SQL error log relating to the suspect database.
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
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply