Magic Self-Fixing DBCC Error! 2

[JJayUK]

Hi,

We currently run SQL Sever 7 with SP3. The database I'm concerned with is quite large (about 50gig) and executes a long batch run every night.

At the end of each backup a DBCC CHECKDB is performed and ,every night, produces an index error on one of the tables. The next morning, we can simply re-run the DBCC CHECKDB or DBCC CHECK TABLE and the DBCC returns no errors.

Has this happened to anyone else? There seems to be no sign of the problem in any SQL documentation.

Any help appreciated, Thanks.

 

[michaelkrauklis]

I have an unrelated phantom problem. My database is considerably smaller than yours, only about a gig. We do weekly backups and will soon be changing to a nightly backup schedule. When we do the backup we get an error message that the backup has failed on two of the views, but if we restore the views it said that it failed on are there in their entirety. It says it fails but doesn't... but I guess that's better than if it failed and didn't tell us. Sometimes things are just quirky. Good luck. MYenigmaSELF
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

 

[RickCole]

From your scenario I would assume your long batch run is somehow messing up an index. The following DBCC run which reports the error also fixes the problem. Therefore your morning run has no errors to return.

What error messages from your evening run do you get returned?

Rick.

 

[JJayUK]

I thought DBCC only fixes errors if you tell it too. Our DBCC is just a check - am I being naive here?

We don't get error messages from the batch run itself, just from the DBCC check. The error returned is usually a 'keys out of order' error on an index, but not necessarily the same one.

Do you think SQL Server cannot properly cope with long batch runs? This run usually lasts 6 hours and shifts huge amounts of data. Is this too much?

Thanks very much,

 

[tlbroadbent]

SQL should be able to handle a six hour run that shifts lots of data. Do you reindex after the batch run completes or after the DBCC?

Indexes can become fragemented when data is moved, particulary if clustered index columns are modified. Inserting a lot of records can also cause index fragmentation. We reindex many tables nightly using DBCC DBREINDEX.


What is the exact error message received? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[JJayUK]

The exact message receieved is:

(For security reasons I have to remove the real names of the tables and fields)

Msg 8951, Sev 16: Table Corrupt: Table '<TABLENAME>' (ID 1970106059). Missing or invalid key in index 'INDEXNAME' (ID 3) for the row: [SQLSTATE 42000]
Msg 8955, Sev 16: Data row (1:5909529:0) identified by (RID = (1:5909529:0) ) has index values (FIELDNAME1 = 'DATA ' and FIELDNAME2 = 'DATA'). [SQLSTATE 42000]
Msg 8952, Sev 16: Table Corrupt: Database 'DBNAME', index 'INDEXNAME' (ID 1970106059) (index ID 3). Extra or invalid key for the keys: [SQLSTATE 42000][SQLSTATE 42000]

Msg 8956, Sev 16: Index row (1:5915616:166) with values (FIELDNAME1 = 'DATA ' and FIELDNAME2 = 'DATA') points to the data row identified by (RID = (1:5909528:0)).
This is interesting. My (rather embarrassed) answer is no we do not run DBCC REINDEX. We never have done and I will check this as a possible solution.

Thanks again for your help.

 

[RickCole]

I think DBCC CHCEKDB will automatically fix any straightforward errors it finds as long as there is no possibility of data corruption.

I'm can't remember where I know this from as it's not in any documentation I can find. Hopefully somebody else might be able to confirm this one??

We run DBCC REINDEX on a regular basis to de-frag our SQL tables and if your moving lots of data around, it’s definitely something you should be doing.

Look at introducing REINDEX his as part of your database maintenance plans and see if this cures your error.

Rick.

 

[tlbroadbent]

SQL 7 BOL states, &quot;DBCC CHECKDB is the safest repair statement because it catches and repairs the widest possible errors.&quot; (emphasis added) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[JJayUK]

Thanks for this. It probably explains why we get errors the first time we run it but not the second.

We're trying the DBCC DBREINDEX command but I think it will take too long. I'm beginning to believe that the shear amount of data (one table has about 22 Million rows) is probably the reason we're getting corrupt indexes in the first place.

One of the unusal things is this problem only started when we upgraded our hardware. A brand new server was purchased to put the system on and it's only since then that we've been getting this problem. The disk caching is definitly switched off but the new server was specked with less memory than the old one. Could this be a problem?

 

[RickCole]

Well SQL Server does love memory that’s for sure.

In theory it shouldn't cause corrupt indexes but I seem to recall having a problem rebuilding a clustered index on a 6.5 database. On this occasion it happened as the table increased in size and blow my weekly re-index maintenance job.

The server in question was of a very poor spec and long over due an upgrade. Once upgraded the error was resolved.

I wouldn't say it will cure your error but if you can get hold of some extra memory it's worth a test.

Also have you got tempdb set to a good size?? Seem to recall tempdb must be at least one and half times the size of your largest table.

Rick.