May 3, 2011 at 3:52 am
Hi all
I had a problem in my SQL server 2008 R2. I got an error on a nonclustered index so I tried to recreated it. I deleted it wihtout problems but when I tried to create it again, after 2 hours an a half, I got this error:
The statement has been terminated.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
The table is really huge (around 300GB). How could I create this index again?
This is the query I ran:
CREATE NONCLUSTERED INDEX [IX_table] ON [dbo].
(
[number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
What can I do to create this index again? Is there any option to improve this query?
Thanks.
John
May 3, 2011 at 4:02 am
Hi John,
You don't see anything in the SQL log?
All your tables are located on the PRIMARY file group?
If you can exclude ressource issues (disk, ram, cpu), you could try to turn off the SORT_IN_TEMDB switch.
Maybe run a checkdb as well.
Hope this helps...
Olivier
May 3, 2011 at 4:27 am
is there Clustered index on this table ? if not then create it first then create non-clustered
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 3, 2011 at 4:31 am
That error can indicate corruption. Please run the following and post any results.
DBCC CheckDB(<Database name>) WITH No_INFOMSGS, ALL_ERRORMSGS
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
May 3, 2011 at 5:29 am
The table has already a clustered index.
from the logs, I can see there was a dump :
*
* BEGIN STACK DUMP:
* 05/02/11 19:05:32 spid 3112
*
* ex_raise2: Exception raised, major=52, minor=42, state=4, severity=22
*
* Input Buffer 510 bytes -
* /****** Object: Index [IX_momt_msisdn] Script Date: 05/
* 02/2011 11:37:36 ******/ CREATE NONCLUSTERED INDEX [IX_momt_msisdn] ON
* [dbo].[momt] ( [msisdn] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NOR
* ECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTIN
* G = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
* [PRIMARY]
Is it too big so it run out of memory perhaps?
May 3, 2011 at 5:46 am
Error 5242: An inconsistency was detected during an internal operation in database <database name> on page <Page Number>. Please contact technical support.
Have you run that checkDB as asked?
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
May 3, 2011 at 6:07 am
I ran a
DBCC CHECKTABLE ("table") WITH PHYSICAL_ONLY;
cause the DB is really big and I got these errors:
Msg 8944, Level 16, State 16, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594537443328 (type Unknown),
page (44:796483), row 0. Test (VarColOffsets + (int)(sizeof (VarColOffset) * nVarCols) <= (nextRec - pRec)) failed.
Values are 105 and 103.
Msg 8944, Level 16, State 16, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594537443328 (type Unknown), page (44:796483), row 0. Test (VarColOffsets + (int)(sizeof (VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 105 and 103.
Msg 8944, Level 16, State 18, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594537443328 (type Unknown), page (44:796484), row 0. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 109 and 117.
Msg 8944, Level 16, State 18, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594537443328 (type Unknown), page (44:796484), row 0. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 109 and 117.
Msg 8944, Level 16, State 18, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594537443328 (type Unknown), page (44:796485), row 0. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 149 and 155.
Msg 8944, Level 16, State 18, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594537443328 (type Unknown), page (44:796485), row 0. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 149 and 155.
Msg 8941, Level 16, State 1, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594537443328 (type Unknown), page (44:796486). Test (sorted .GetOffset () >= PageHeaderOverhead ()) failed. Slot 26, offset 0x4f is invalid.
Msg 8942, Level 16, State 1, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594537443328 (type Unknown), page (44:796486). Test (sorted.GetOffset () >= max) failed. Slot 0, offset 0x1a overlaps with the prior row.
Msg 8944, Level 16, State 18, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594537443328 (type Unknown), page (44:796487), row 0. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 109 and 117.
Msg 8944, Level 16, State 18, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594537443328 (type Unknown), page (44:796487), row 0. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 109 and 117.
CHECKTABLE found 0 allocation errors and 10 consistency errors not associated with any single object.
Msg 8928, Level 16, State 1, Line 2
Object ID 631673298, index ID 1, partition ID 72057594416332800, alloc unit ID 72057594538950656 (type In-row data): Page (44:796480) could not be processed. See other errors for details.
Msg 8944, Level 16, State 18, Line 2
Table error: Object ID 631673298, index ID 1, partition ID 72057594416332800, alloc unit ID 72057594538950656 (type In-row data), page (44:796480), row 0. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 181 and 187.
Msg 8944, Level 16, State 18, Line 2
Table error: Object ID 631673298, index ID 1, partition ID 72057594416332800, alloc unit ID 72057594538950656 (type In-row data), page (44:796480), row 0. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 181 and 187.
Msg 8928, Level 16, State 1, Line 2
Object ID 631673298, index ID 1, partition ID 72057594416332800, alloc unit ID 72057594538950656 (type In-row data): Page (44:796481) could not be processed. See other errors for details.
Msg 8944, Level 16, State 18, Line 2
Table error: Object ID 631673298, index ID 1, partition ID 72057594416332800, alloc unit ID 72057594538950656 (type In-row data), page (44:796481), row 0. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 141 and 145.
Msg 8944, Level 16, State 18, Line 2
Table error: Object ID 631673298, index ID 1, partition ID 72057594416332800, alloc unit ID 72057594538950656 (type In-row data), page (44:796481), row 0. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 141 and 145.
Msg 8928, Level 16, State 1, Line 2
Object ID 631673298, index ID 1, partition ID 72057594416332800, alloc unit ID 72057594538950656 (type In-row data): Page (44:796482) could not be processed. See other errors for details.
Msg 8939, Level 16, State 7, Line 2
Table error: Object ID 631673298, index ID 1, partition ID 72057594416332800, alloc unit ID 72057594538950656 (type In-row data), page (44:796482). Test (m_freeData >= PageHeaderOverhead () && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 8191 and 8158.
Msg 8928, Level 16, State 1, Line 2
Object ID 631673298, index ID 1, partition ID 72057594416332800, alloc unit ID 72057594538950656 (type In-row data): Page (44:796483) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 2
Object ID 631673298, index ID 1, partition ID 72057594416332800, alloc unit ID 72057594538950656 (type In-row data): Page (44:796484) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 2
Object ID 631673298, index ID 1, partition ID 72057594416332800, alloc unit ID 72057594538950656 (type In-row data): Page (44:796485) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 2
Object ID 631673298, index ID 1, partition ID 72057594416332800, alloc unit ID 72057594538950656 (type In-row data): Page (44:796486) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 2
Object ID 631673298, index ID 1, partition ID 72057594416332800, alloc unit ID 72057594538950656 (type In-row data): Page (44:796487) could not be processed. See other errors for details.
CHECKTABLE found 0 allocation errors and 13 consistency errors in table 'table' (object ID 631673298).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (DB.dbo.table).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
May 3, 2011 at 6:10 am
I guess you might be ready to accept some data loss: minimum repair level is repair_allow_data_loss
May 3, 2011 at 6:15 am
Time to check your backup/recovery strategy...
What's your last known good backup?
Does your backup/recovery strategy allows for point-in-time recovery?
Was backup/recovery strategy ever tested?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 3, 2011 at 6:19 am
Run CheckDB (not checkTable) with the options I specified, NOT with physical_only. There's corruption and we need a full list of everything damaged before we can plan a solution.
What recovery model?
When was your last backup? (please restore that last backup somewhere else and run CheckDB on it too)
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
May 10, 2011 at 2:47 am
Sorry for my late reply.
Finally my hard disk failed and I had to recover the database from the backups.
Thanks to all for your help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply