December 2, 2008 at 2:56 am
This is the full code that I was supposed to post 🙂
CREATE
INDEX [IDXOUT1_54] ON [dbo].[OUT_54] ([YEAR], [MONTH], [DAY], [HOUR], [UPLANLINEID])
WITH
DROP_EXISTING
ON [PRIMARY]
GO
CREATE
INDEX [IX_OUTP_54] ON [dbo].[OUT_54] ([YEAR], [MONTH], [DAY], [HOUR], [UPLANBUSID], [ITERATION_NO])
WITH
DROP_EXISTING
ON [PRIMARY]
GO
DBCC CHECKDB
GO
Hope this works and thanks for the help Gail :). I will post once, the CHECKDB completes.
Regards,
December 2, 2008 at 4:32 am
No errors 😀 :w00t:
The command(s) completed successfully.
Paul: Thank you, for all the insights and advice 🙂
Gail: Thank you, for showing me the way 🙂 I will take up the issue of fragmentation with the business folks and get them to get us more space.
Thank you, folks!
Regards,
December 2, 2008 at 6:12 am
hemanth.damecharla (12/2/2008)
No errors 😀 :w00t:
Excellent!
It may be an idea to run checkDB a little more frequently. Weekly, if you can afford the time, otherwise do some databases one weekend, more the next, etc. It should be a scheduled job run regularly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2008 at 6:42 am
Hmm - I don't think the corruption was in the nonclustered indexes - I think the corruption was (and still is) in the base table itself. All the "missing" NC index rows are mapping to two pages worth of data in the base table - and the values of the columns that the index is supposed to have as its keys look quite nonsensical to me - almost as if the month, day etc are being stored as floating-point numbers, but there's been an arithmetic error that's persisted the values as almost-zero but not exactly zero.
Of course, rebuilding the nonclustered indexes will pick up the corrupt values as the new index keys, and hence remove the symptom of the corruption. That's not what I would have done first in this case - I'd investigate the base table corruption. Of course, these values may be perfectly ok as far as your business logic is concerned...do those data values look right to you?
So - two things to investigate: 1) is the actual data ok - in which case you've hit a (probably known) bug that stopped the NC index picking up some rows 2) if the data isn't ok - how did the data in the base table get corrupt
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 2, 2008 at 11:13 am
Paul Randal (12/2/2008)
Hmm - I don't think the corruption was in the nonclustered indexes - I think the corruption was (and still is) in the base table itself. All the "missing" NC index rows are mapping to two pages worth of data in the base table - and the values of the columns that the index is supposed to have as its keys look quite nonsensical to me - almost as if the month, day etc are being stored as floating-point numbers, but there's been an arithmetic error that's persisted the values as almost-zero but not exactly zero.Of course, rebuilding the nonclustered indexes will pick up the corrupt values as the new index keys, and hence remove the symptom of the corruption. That's not what I would have done first in this case - I'd investigate the base table corruption. Of course, these values may be perfectly ok as far as your business logic is concerned...do those data values look right to you?
So - two things to investigate: 1) is the actual data ok - in which case you've hit a (probably known) bug that stopped the NC index picking up some rows 2) if the data isn't ok - how did the data in the base table get corrupt
Thanks
Hmm...that's a dampner :)...I will check the data and report back to you in 8hrs 😀
December 2, 2008 at 11:29 am
Paul Randal (12/2/2008)
Hmm - I don't think the corruption was in the nonclustered indexes - I think the corruption was (and still is) in the base table itself.Of course, rebuilding the nonclustered indexes will pick up the corrupt values as the new index keys, and hence remove the symptom of the corruption. That's not what I would have done first in this case -
Oops. Sorry. :unsure:
How would something like that happen? To near-zero some columns in the table on two pages leaving other columns intact (the year still looks valid) and without damaging the page or row headers....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2008 at 8:40 pm
After a little bit of research revealed that the data is intact and the fields for date, month and hour have numeric values because these tables are used for datamining purposes. So, all is good 🙂
December 2, 2008 at 8:55 pm
oh - ok then - Gail was right. The data in those fields looked so strange that it looked corrupt to me - being infinitesimally small rather than zero.
So - there is still the problem of why the NC indexes became corrupt in the first place - there have been some bugs in earlier SPs where parallel index rebuild plans caused NC index rows to be dropped - I wonder if you hit one of those?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 3, 2008 at 2:16 am
Any links to the information on these bugs? 🙂
December 3, 2008 at 7:05 am
Not off the top of my head - Google 'sql kb index rebuild corruption' and you'll get some links.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply