text, ntext or image error

Last Post 02 Mar 2011 06:15 PM by rm. 30 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Page 1 of 212 > >>
Author Messages Not Resolved
dstpaul
New Member
New Member

--
04 Feb 2011 09:49 AM
We have a 30GB SQL database.  A few months back we had a Raid5 drive failure - I noticed weeks later that the nightly index quit running.  Restoring is obviously not an option.  Is there a DB repair tool to fix the issue
russellb
New Member
New Member

--
04 Feb 2011 11:28 AM
First, what error(s) are you getting? Did you run DBCC CHECKDB() against your databases? What error messages did it show?
gunneyk
New Member
New Member

--
04 Feb 2011 11:28 AM
If its actual corruption in the data itself there is little you can do without risk of data loss. This is why running DBCC CHECKDB on a regular basis is essential to catch errors as soon as possible so you can utilize backups and minimize data loss. Especially after disk crashes which are the primary source of data corruption. In any case run DBCC CHECKDB with the basic options first to see what it says: DBCC CHECKDB WITH NO_INFOMSGS;
Then if it shows errors you can try running DBCC CHECKDB REPAIR_REBUILD . This will fix minor issue with no data loss.
But this sounds like its beyond that and you might need to run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS WITH NO_INFOMSGS;

FYI there is a series that Paul wrote on DBCC that you might want to read up on as well: http://www.sqlskills.com/BLOGS/PAUL...Angle.aspx
dstpaul
New Member
New Member

--
04 Feb 2011 01:22 PM

Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 3340726566912 owned by data record identified by RID = (1:6282:2) id = 466100701 and indid = 6.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 3340322668544 owned by data record identified by RID = (1:6638:1) id = 302272482 and indid = 1.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:2974986), slot 7, text ID 3340726566912 does not match its reference from page (1:6638), slot 1.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 2, text, ntext, or image node page (1:2974986), slot 7, text ID 3340726566912 is pointed to by page (1:6638), slot 1 and by page (1:6282), slot 2.
Server: Msg 8964, Level 16, State 1, Line 1

Page (1:2974986), slot 7 for text, ntext, or image node does not exist

gunneyk
New Member
New Member

--
04 Feb 2011 02:08 PM
Any indid's > 1 are nonclustered indexes and you shoudl be able to repair any problems with corruption in the actual index by dropping and recreating or running the CHECKDB with the REPAIR_REBUILD option. The IndID = 1 is the clustered index though and its likely you will lose some data there. Did you run with the REPAIR_REBUILD option yet?
dstpaul
New Member
New Member

--
06 Feb 2011 09:08 AM
I WILL ATTEMPT ON THE DATABASE COPY
dstpaul
New Member
New Member

--
06 Feb 2011 09:12 AM
is there a way to find out which table contains data that may be corrupt
rm
New Member
New Member

--
06 Feb 2011 11:15 AM
Run 'select object_name(2)' under that db, it'll give you table name.
dstpaul
New Member
New Member

--
07 Feb 2011 07:00 AM

sysindexes

rm
New Member
New Member

--
07 Feb 2011 12:15 PM
Did you try rebuild indices?
dstpaul
New Member
New Member

--
08 Feb 2011 09:31 AM
I tried dbcc checkdb ('database', repair_rebuild)
gunneyk
New Member
New Member

--
08 Feb 2011 11:14 AM
And what were the results?
dstpaul
New Member
New Member

--
08 Feb 2011 11:41 AM
Listed above in the fourth post
rm
New Member
New Member

--
08 Feb 2011 12:18 PM
Backup the db, rebuild all indices in it then run dbcc again.
gunneyk
New Member
New Member

--
08 Feb 2011 03:16 PM
I seem to remember that the rebuild option would say if it fixed the issue or not. Have you run anotehr checkdb to see if the corruption was actually fixed? If not and you really did run the rebuild option you will most likely need to use the allow_data_loss option. But backup first.
Dunbar
New Member
New Member

--
11 Feb 2011 06:40 AM
so I should run I tried dbcc checkdb ('database', allow_data_loss) - will it list the repaired table and show the data that's lost
gunneyk
New Member
New Member

--
11 Feb 2011 01:07 PM
It will not show you the actual data that was lost. It may specify an object and page ID but it will not list the data.
Dunbar
New Member
New Member

--
14 Feb 2011 01:46 PM
I ran it on the play database - it ran all weekend and never finished
gunneyk
New Member
New Member

--
15 Feb 2011 05:51 AM
Was it being blocked? It needs exclusive access to the database to run. otherwise it will sit there and wait.
dstpaul
New Member
New Member

--
15 Feb 2011 07:32 AM
I ran it this way

ALTER DATABASE play SET SINGLE_USER
DBCC CHECKDB('play', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE play SET MULTI_USER
You are not authorized to post a reply.
Page 1 of 212 > >>


Acceptable Use Policy