question

Apeman avatar image
Apeman asked

Database keeps getting corrputed

Hi,

I have a problem with an 2K5 (v9.0.3042) database. For about 3 tables, several nonclustered indexes are getting corrupt every week. I droppped and recreated these indexes, did a DBCC CHECKDB and everything was ok, but the problem re-appears after several days. Tables and indexes which are giving problems are always the same. The only thing I see is that autoshrink is enabled (low diskspace)

Any help would be appreciated!

Wilfred

dbccmaintenancecorruption
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
venkatreddy avatar image
venkatreddy answered

Hi,

In SQL-2000,for a self updated query which is updating huge number of records we may find that non-clustured indexes is corrupted.In this case B-tree structure for the non-clustured indexes also corrupts.You may notice database corruption sometimes.Applying latest service packs on OS and SQL is one option to recover.second restore NCI back from the backup sets.May i know which DBCC command you ran?

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

That's a very abnormal situation. Are you sure you're on the latest service packs on your OS and on SQL Server? Are you getting errors prior to, or in conjunction with the corruption?

Honestly, something like this, I'd call in the Marines, or Paul Randal.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Apeman avatar image Apeman commented ·
It's MSSQL 2005 with SP2.
0 Likes 0 ·
sp_lock avatar image
sp_lock answered

Grants suggestion of getting Paul in is a good suggestion.. Also search through his blog as he pretty my mind dumps all his knowledge on DBCC to there.

http://www.sqlskills.com/BLOGS/PAUL/category/Conferences.aspx

http://www.sqlskills.com/BLOGS/PAUL/category/Repair.aspx

What DBCC CHECKDB command are you running?

In some cases it it possible that the file system is at fault. I would do the necessary check on the disks themselves too.

4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Yes, absolutely. It really could be hardware. I should have thought of that. +1
0 Likes 0 ·
Apeman avatar image Apeman commented ·
DBCC CheckDB() with NO_INFOMSGS
0 Likes 0 ·
Blackhawk-17 avatar image Blackhawk-17 commented ·
Get your O/S Admins to do a full hardware diagnostic. It could be drives, RAM or something else. A single bit switched in transit...
0 Likes 0 ·
Steve Jones - Editor avatar image Steve Jones - Editor ♦♦ commented ·
I would lean towards hardware as well. I can't imagine that SQL Server corrupts a table in particular because of data in it or some command being run
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.