| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2007-05-09 : 11:01:26
|
| Okay, so i have a tabase named "TEST" that i set up, everything in it is for a website i've been designing slowly for the last month, i come in today, and notice the database is marked suspect. look at the log and i see all of this:Recovery of database 'Test' (7) is 20% complete (approximately 305 more seconds) (Phase 2 of 3).ForwardLogBlockReadAheadAsync: Operating system error 23(Data error (cyclic redundancy check).) encountered.Operating system error 23(Data error (cyclic redundancy check).) on device 'C:\Program Files\Microsoft SQL Server\MSSQL\data\Test_Log.LDF' during ForwardLogBlockReadAheadAsync.Error: 3313, Severity: 21, State: 2Error while redoing logged operation in database 'Test'. Error at log record ID (8440:42526:1)..Error: 3414, Severity: 21, State: 1Database 'Test' (database ID 7) could not recover. Contact Technical Support..Now, honestly, i could care less about the data, the thing the matters most to me, is the couple of stored procedures i've developed.what can i do? |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-09 : 11:04:21
|
| Don't you have backup of the database? Don't you store your stored procs as a scripts in version control system?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-09 : 11:04:57
|
| Set it to emerggency mode.in v2005 that's an alter databasein v2000 seehttp://www.nigelrivett.net/SQLAdmin/RecoverCorruptDatabase.htmlThen you should be able to use sp_helptext to get the SP's - if not look in syscomments.and what harsh_athalye said.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-05-09 : 11:10:18
|
quote:
Don't you have backup of the database? Don't you store your stored procs as a scripts in version control system?
no, as i said i've been develpoing software, i wasnt finished yet so i didnt set up the backup. SMART hu? lesson learned...dont know what "Version Control" is, but i'd love to know for future ref. i ususally just build/alter the procedures in query analyzer and that's it.NR, i'll start reading, thank you, and i'll get back to you guys. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-09 : 11:31:08
|
| Always save the script to disk. Periodically update sourcesafe with that script.Also helps that you don't mind making major changes to the sp - just update sourcesafe first and you can easily go back to the previous version if it doesn't work out.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-05-09 : 11:34:35
|
| whew. well, atleast now i have my code.okay, so question. can the users that are piloting my page still use the database? that way i can set everything up to restore tonight when everyone is gone and it wont really affect us much.before the server went down, i was running a update that the last time i ran literally took 7 days. it was about 72 hours into the update when the server re-booted. now the good thing, atleast i hope, is that the table the update is ran on, is a table none of the users us. so they SHOULD be okay right? |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-05-09 : 11:43:18
|
| Also, "cyclic redundancy check"doesnt that usually point to disk failure? or could it be just because the server B.S.O.D.'ed and re-booted? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-09 : 12:48:05
|
| "can the users that are piloting my page still use the database"In reality no; the likelihood is that it will go from bad to worse, no sense carrying on with corruptions in your database.You can just go with DBCC CHECKDB in REPAIR mode and let it kills off whatever data is corrupted. But that may leave your FKs etc. in an inconsistent state.Or you can have a go at coping out the "salvageable" data to a new, clean, database:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How+I+Rescue+Data+from+Corrupted+Databases"doesnt that usually point to disk failure?"Some sort of hardware failure, yes. I wouldn't expect a power-failure or BSOD to trash the database - the Log file, and the "Recovery of database ..." during startup, should make those sorts of failures benign.Kristen |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-05-09 : 13:05:08
|
| thank you Kristen. at this point, i'm just doing back flips from getting my code back. i've already detached the database, and began setting up the scripts to copy everything back to a clean database from one of my other servers.i plan on hooking up some extra drives i have laying around to set up a backup-disk array so that i dont have to go though this B.S. if it happens again :o) Thank you guys for all your help! |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-05-10 : 00:31:58
|
| One last question, it seems that in emergency mode, i cant Script the database because the onlything under the database is a Node that says "(No Items)" is there anyway to do this manually? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-10 : 03:56:17
|
| You could try:back up the databaserestore that to a "temp" database (which will presumably still be "suspect")"repair" the Temp database (with data loss etc.)script the Temp database.Kristen |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-05-10 : 12:28:41
|
| will do, thank you again. |
|
|
|
|
Suspect database