x

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

more ▼

asked Mar 17, 2010 at 07:22 AM in Default

Apeman gravatar image

Apeman
311 13 13 15

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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?

more ▼

answered Mar 17, 2010 at 08:01 AM

venkatreddy gravatar image

venkatreddy
562 28 29 32

it's MSSQL 2005
Mar 17, 2010 at 08:40 AM Apeman
hey,u can do the same for 2K5.y did u voted in negative?
Mar 17, 2010 at 09:19 AM venkatreddy
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 17, 2010 at 07:44 AM

sp_lock gravatar image

sp_lock
9.3k 25 28 31

Yes, absolutely. It really could be hardware. I should have thought of that. +1
Mar 17, 2010 at 08:20 AM Grant Fritchey ♦♦
DBCC CheckDB() with NO_INFOMSGS
Mar 17, 2010 at 08:35 AM Apeman
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...
Mar 17, 2010 at 04:22 PM Blackhawk-17
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
Mar 18, 2010 at 03:53 PM Steve Jones - Editor ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 17, 2010 at 07:30 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

It's MSSQL 2005 with SP2.
Mar 17, 2010 at 08:39 AM Apeman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x47
x37
x19

asked: Mar 17, 2010 at 07:22 AM

Seen: 1134 times

Last Updated: Mar 17, 2010 at 07:22 AM