| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2004-09-29 : 11:50:04
|
HelloMy colleague and I managed to reattach a database (after detaching the DB and beforehand, deleting a transaction log file).We used a method of creating a new database with the same name of the old db and changing the name of the orginal, associating the so called missing log file with the new database, deleting both the mdf and ldf files, and then renaming the original. This in itself did not work, and so we had to rebuild the log file using the command dbcc rebuild_log as a last resort (although reading that there were risks with this method). Finally, we managed to reattach the database, much to our relief. Or so I thought.................I have now tried to run a query against the database (below), and received the following error message. I have done some searching on the web, and the outlook seems pretty bleak. Apart from reverting to backups, are there any suggestions?The Query: use [Trigger]select * from J36where exists (select null from J36 Jwhere J.CycleInstanceId = J36.CycleInstanceIdand J.AccountNo = J36.AccountNoand J.MobileNo = J36.MobileNogroup by J.CycleInstanceId, J.AccountNo, J.MobileNohaving J36.J36ID < max(J.J36ID))The error message: Server: Msg 823, Level 24, State 2, Line 3I/O error 23(Data error (cyclic redundancy check).) detected during read at offset 0x000000167a4000 in file 'F:\MDF\Trigger\Trigger_Data.MDF'.Connection BrokenHearty head pats |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-29 : 18:12:05
|
| Run DBCC CHECKDB on that database to determine if there is database corruption.Tara |
 |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-09-30 : 03:46:37
|
| Thanks for your reply!But I already did that and no errors came up. What I am going to do is run a query that I ran before the re/de-attachment disaster and see if a similar error message appears, then I can determine whether this was a problem before or after re/de-attaching the database.Watch this space!Hearty head pats |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-09-30 : 04:20:06
|
| Oh dear, just tried to run some other queries and got the same message. Found this link:http://support.microsoft.com/default.aspx?scid=kb;en-us;826433Trying to decipher it now as it is written in Nerd (that is not a slander, but my way of saying that it is very technical and that I do not understand it. I am desperately trying to learn nerd, but it is a long and slow process).In the meantime, if anyone has experienced a similar problem, please feel free to offer anything you can think of! ThanksHearty head pats |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-30 : 12:19:51
|
| Are you service packed on that database server?Tara |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-09-30 : 12:27:57
|
Sorry for being a database dunce, but what does service packed mean exactly? If I don't reply to your next post, it is because it is home time. But I shall be back tomorrow!Unfortunately, my colleague will not be here, and I have been given the task to sort out the DB (as the client will be requesting important data next week) Yikes! Really rather nervous about it, but what better way to learn than been thrown in the frying pan!Could possibly be a hard disk failure?Hearty head pats |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-30 : 12:30:05
|
| Service packs are bug fixes that are rolled up into a release. SQL Server 2000 is up to service pack 3a. There is also security patch 818 that should be installed as well. So you might be encountering problems with SQL Server 2000 that have already been fixed with a service pack. So if you don't have these installed, install them now. Service pack 3a then security patch 818. Both can be downloaded from the MS web site. If these are already installed, then I'd look the Application Log in Event Viewer to determine if you have any hardware problems.Tara |
|
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-09-30 : 16:25:21
|
where are you runnig the query from?I have had that situation maybe twice and running from the SQL Server Enterprice Manager and I - closed the aplication and reopened it
- selected connect in the enterprice manager (right click on the server \ connect)
and it kept working ok*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-10-01 : 05:26:47
|
| Morning GuysThanks for your replies. I have located the source of the problem, and unfortunately, it is a disk isue. We have a bad block which has corrupted some of the data. I am copying all the mdf files back to the original disk location, and we are going to reformat the disk. However, the data located on the bad block may be corrupt. Are there any utilities available that can perhaps recitfy the data?ThanksHearty head pats |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-01 : 05:32:41
|
| use the original file you were trying to attach before? or was that the one corrupted? |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-10-01 : 05:38:19
|
| Unfortunately, we transferred the original files over to the new location. What a palava! Do you know, this whole situation arose because we ran out of room. I was nervous about doing a query without backup, therefore, we moved the data to a new disk, so i could perform a back up and then run the query. Low and behold,this has happened without having the chance to perform a backup (let this be a lesson to you boys and girls...........always backup!!!!).I am doing a simple copy of the files over to the new location, and I am aware that this will copy the faulty data. I am doing some research now, but if anyone knows of any techniques (perhaps using DTS) to either repair or copy uncorrupted files, it would be most appreciated.Oh, and when I ran a dbcc checkDb, it came up with no errors, why it that if there were errors?ThankyouHearty head pats |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-10-01 : 05:39:28
|
| sorry, noticed a mistake, I am copying the files back from the new location to the old location!Hearty head pats |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-01 : 05:48:34
|
| shouldn't you copy the file from the old location to the new one? or if the bad block is on the new location and you still have the file on the old location, then you can use the "old" file and copy it to a hd without the bad blocks. |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-10-01 : 06:13:32
|
| Sorry if I am confusing you! I shall try and make myself clear. We transferred the mdf files from the full disk (the old one) to a new disk. We then deleted the files from the old disk. Only when I ran a query did i realise that there was a problem with the data on the new disk. Therefore, I am taking the files off the new disk, to place back on the old disk which will use up all the disk space, but is at least not corrupt. The plan is to reformat the new disk, and then transfer the files back to it.However, in the meantime, I have some corrupt data in one of the mdf files, therefore, am wondering what I can do to repair the damage.ThanksHearty head pats |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-01 : 06:20:56
|
quote:
Originally posted by Bex
Sorry if I am confusing you! I shall try and make myself clear. We transferred the mdf files from the full disk (the old one) to a new disk. We then deleted the files from the old disk. Only when I ran a query did i realise that there was a problem with the data on the new disk. Therefore, I am taking the files off the new disk, to place back on the old disk which will use up all the disk space, but is at least not corrupt. The plan is to reformat the new disk, and then transfer the files back to it.However, in the meantime, I have some corrupt data in one of the mdf files, therefore, am wondering what I can do to repair the damage.ThanksHearty head pats
if the file fell on a bad block then i wish you luck...i think i already stumble on how to repair corrupt mdf files, just having difficulty remembering where... if i do remember it, i'll post it here, try google on repair corrupt files though |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-10-01 : 07:23:56
|
| Thanks mate! I shall have a look now! Incidently, when copying the files back to the old disk, I chose not to detach and attach the database (due to fear of what happened last time). However, I want to see what happens when I run some queries against these newly copied files to see what happens (and leave the other disk well alone for my colleague to deal with...tee hee, when the going gets tough, simply pass the buck!).Anyway, the path reference is to the files on the new disk, and I want to change this path to reference the files I have copied over. I cannot change the sysfiles entries, so do you know how I can them change manually?Hearty head pats |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-01 : 08:04:12
|
quote:
Originally posted by Bex
Anyway, the path reference is to the files on the new disk, and I want to change this path to reference the files I have copied over. I cannot change the sysfiles entries, so do you know how I can them change manually?Hearty head pats
i believe you should have a. detached the databases first then copied them over, so that when you attach them again, you can change the physical location, orb. made a backup and copied this backup to the new location and restored it with move optionanyways if you want to attach the copied files, you can always try, attachment has an option for moving the physical location  if you really want to experiment, you can create a new database of the same name, then stop sql agent, replace the files with your copied ones, then start sql agent. i expect database will go suspect, and then use nigel's recovery script for suspect database.  |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-10-01 : 09:08:41
|
| Brilliant! Thanks for all your help, it is much appreciated. I'll know when I am finally becoming more database adept when I too can write sensible and sensical replies to peoples questions. Hearty head pats |
|
|
|
|
Problems with reattached database