Table Corruption

Question

Table Corruption

jabarabd

Newbie

Points: 3
More actions
February 15, 2005 at 11:26 pm

#92444

I have a table with 6.6 million records, 333 columns with insert and select operations against it. No update or delete. No cluster index, just some non-clustered indexes. New records inserted at a rate of 30 per minute 24x7 non-stop. Every week or so, we alter table to add about 1 column.
We hv table corruption on average once every 2 months and will do dbcc checktable to repair.
My questions.
1. How can i prevent corruption on the table ? Any good practices or recommendations ? We hv full design control on the table.
2. How to interpret the results from the dbcc checktable command below?
Server: Msg 8977, Level 16, State 1, Line 1
Table error: Object ID 1665193478, index ID 1. Parent node for page (3:426169) was not encountered.
Server: Msg 8977, Level 16, State 1, Line 1
Table error: Object ID 1665193478, index ID 1. Parent node for page (3:426170) was not encountered.
Server: Msg 8977, Level 16, State 1, Line 1
Table error: Object ID 1665193478, index ID 1. Parent node for page (3:426171) was not encountered.
Server: Msg 8977, Level 16, State 1, Line 1
Table error: Object ID 1665193478, index ID 1. Parent node for page (3:426172) was not encountered.
Server: Msg 8977, Level 16, State 1, Line 1
Table error: Object ID 1665193478, index ID 1. Parent node for page (3:426173) was not encountered.
Server: Msg 8977, Level 16, State 1, Line 1
Table error: Object ID 1665193478, index ID 1. Parent node for page (3:426174) was not encountered.
Server: Msg 8977, Level 16, State 1, Line 1
Table error: Object ID 1665193478, index ID 1. Parent node for page (3:426175) was not encountered.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 1665193478. To see all error messages rerun the statement using "WITH ALL_ERRORMSGS".
        The error has been repaired.
        The error has been repaired.
        The error has been repaired.
        The error has been repaired.
        The error has been repaired.
        The error has been repaired.
        The error has been repaired.
Repair: Page (3:426265) has been deallocated from object ID 1665193478, index ID 0.
Repair: Page (3:967876) has been deallocated from object ID 1665193478, index ID 0.
Clustered index successfully restored for object 'dbo.module' in database 'LogDatabase'.
There are 6623014 rows in 2252109 pages for object 'module'.
CHECKTABLE found 0 allocation errors and 294 consistency errors in table 'module' (object ID 1665193478).
CHECKTABLE fixed 0 allocation errors and 294 consistency errors in table 'module' (object ID 1665193478).
Thanx........

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

1: You can't prevent corruption, since you never know when your hardware will fail. That's no question of if, but rather when. Best thing is to nake sure you have a good backup and disaster recovery strategy.

2: Table error: Object ID 1665193478, index ID 1.

Index ID 1 *is* a clustered index. And since this index is actually the data in that table, I would call MS PSS to help you here. You might also check your hardware.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jabarabd Newbie Points: 3 More actions · Answer 2

1. yes, u are right. I didnt realise that creating a primary key in EM will automatically create a clustered index.

2. The columns we chose for clustered index is not ideal and will cause a lot of page splits. Will high activity of page splits and add columns caused corruptions ?

thanx....

athurgar SSCrazy Points: 2816 More actions · Answer 3

Lots of page splits could lead to corruption being more likely.

Have you looked at the fillfactor on the indexes and the pad_index factor. This will reduce page splits.

Do you reindex on a regular basis. This may also help reduce possible corruption

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Creating a PRIMARY KEY without explicitely specifying NONCLUSTERED, will always create a CLUSTERED PK in SQL Server.

See if this provides additional help: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Just reread your first post here. May I say that 333 columns in a single table is pretty much. Don't know if this is OLAP stuff, but if it is not, one might think that that table isn't very normalized at all. Might also be worth to keep in mind.

And why do you add another column every week? What the reason for this?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jabarabd Newbie Points: 3 More actions · Answer 5

We collect parametric data from testers running home-built software. Since we introduce many new products, new parametric data needs to be collected.

WHile total columns is 333, on average about 100 different columns are populated, the rests are null.

We did DBCC SHOWCONTIG and results attached below.

The indexes were rebuild some 3 weeks back but it is now highly fragmented.

We suspected our choice of columns for clustered index is not optimum.

we plan to create new column, populate it with running number and make it clustered index. Since record size is 2.5K +/- 0.2K, is there any way to tell SQLServer to allocate say 2 records only per page OR maybe i have to use fillfactor of say 70% ? Any ideas ?

thanx.....

ObjectName	IndexName	IndexId	Pages	Rows	MinimumRecordSize	MaximumRecordSize	AverageRecordSize	ForwardedRecords	Extents	ExtentSwitches	AverageFreeBytes	AveragePageDensity	ScanDensity	BestCount	ActualCount	LogicalFragmentation	ExtentFragmentation
module	PK_module1_new	1	2249287	6708400	2380	2604	2538.066	0	282224	327276	520.4	93.6	85.9	281161	327277	1.2	0.5
module	fidx_station	2	39682	6708400	30	63	39.342	0	5007	10804	1106.8	86.3	45.9	4961	10805	91.8	12.9
module	fidx_WhichTest	3	37946	6708400	25	67	36.51	0	4790	16252	1287.7	84.1	29.2	4744	16253	82.7	23.0
module	fidx_TStamp	4	19990	6708400	9	43	20	0	2514	3607	713.0	91.2	69.3	2499	3608	84.5	16.7
module	fidx_ModulePartNum	5	40862	6708400	29	64	40.864	0	5150	9891	1058.9	86.9	51.6	5108	9892	93.2	12.0
module	idx_operator	6	32280	6708400	22	55	31.009	0	4068	13779	1236.0	84.7	29.3	4035	13780	83.0	22.6
module	oba	7	43802	6708400	27	77	44.52	0	5538	15797	971.3	88.0	34.7	5476	15798	86.5	19.6
module	idx_Result	8	28557	6708400	16	50	27	0	3596	12072	1283.5	84.1	29.6	3570	12073	82.8	23.2
module	idx_Module_Type	9	32693	6708400	12	51	31.619	0	4124	13124	1197.5	85.2	31.1	4087	13125	83.9	22.1

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 6

Wow... 230+ NULL columns to me screams for some data normalization. Pull those columns that are typically NULL out into their own tables (one or more) with the same PK as the main table. Then instead of having a bunch of NULL columns in your main table, when that data doesn't apply to your record, you just don't create a record in the "linked" tables. While this may be a lot of work I bet it would help performance tremendously...