| Author |
Topic |
|
bbasir
Yak Posting Veteran
76 Posts |
 Posted - 2008-04-15 : 13:35:17
|
| I got this message from sql server while creting some 10 indexes on a table that has 13 million rows...I create indexes on a table in a daily job which runs everynight.the indexes that I am creating 4 are on char(1) field.4 are on varchar(10) field.2 are on integer field.I am not sure why it gave me that message?It also asks me to do the following:-A possible database consistency problem has been detected on database 'testdatabase'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database 'testdatabase'.can any one tell me why such error might have happened? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-15 : 13:38:53
|
| Did you run DBCC CHECKDB to determine if there is database corruption?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
bbasir
Yak Posting Veteran
76 Posts |
Posted - 2008-04-15 : 17:06:46
|
| I am going to run it after hours as BOL tells me that not to run DBCC or other such commands during work hours i.e. high load/usage time. Or is there a way to runa compact version of DBCC which doesnot affect the usage times? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-15 : 17:08:54
|
| How big is your database?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
bbasir
Yak Posting Veteran
76 Posts |
Posted - 2008-04-15 : 17:46:32
|
| 12 gb |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-15 : 17:51:18
|
| Although it shouldn't take but a few minutes to run on a database that size, I agree that you should wait until after hours to run it. We run ours daily as a scheduled job in the early morning hours.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
bbasir
Yak Posting Veteran
76 Posts |
Posted - 2008-04-15 : 17:57:11
|
| Thanks, will see what the results are for dbcc. |
|
|
|
bbasir
Yak Posting Veteran
76 Posts |
Posted - 2008-04-15 : 19:57:17
|
| These are the messages that I got, I am not sure if I could try to re-index the table? or run some other command...Server: Msg 8944, Level 16, State 17, Line 1Table error: Object ID 1822629536, index ID 0, page (1:945129), row 13. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 1378 and 354.Server: Msg 2511, Level 16, State 1, Line 1Table error: Object ID 1822629536, Index ID 6. Keys out of order on page (1:1342453), slots 87 and 88.Server: Msg 2511, Level 16, State 1, Line 1Table error: Object ID 1822629536, Index ID 6. Keys out of order on page (1:1367649), slots 146 and 147.There are 13037302 rows in 604736 pages for object 'testtable'.CHECKDB found 0 allocation errors and 4 consistency errors in table 'testtable' (object ID 1822629536). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-15 : 19:59:32
|
| Since you have database corruption, I would suggest posting the entire output of DBCC CHECKDB into the Data Corruption forum here so that Paul Randall (ex-MS employee and author of DBCC CHECKDB) can help you correct it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
bbasir
Yak Posting Veteran
76 Posts |
Posted - 2008-04-16 : 13:13:15
|
| I truncted the table and instead of 10 indexes am not only creating 5 which are utmost needed, and that solved the issue, may be the issue was 10 + indexes on 13 Million + rows was causing the issue. However now everything seems to be fine. |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-04-16 : 14:20:25
|
| The number of indexes shouldn't raise the errors you saw in the DBCC. More likely, by dropping and recreating the indexes, you resolved the problem. I've had corruption on an index and worked with PSS to correct it. It was simply a drop index create index and we were good to go. However, if the errors were on a table, then you would have bigger problems (data loss being only one of them). I believe, from what I've read just about everywhere, this usually results from a hardware issue that occurs during a write process. Just out of curiosity, can you try creating the other 5 indexes and post the results?Terry |
|
|
|
bbasir
Yak Posting Veteran
76 Posts |
Posted - 2008-04-16 : 14:30:25
|
| Is there a way to run hardware diagnostics on the server where sql server resides to see if there was a hardware issue instead of an index issue? This is probably not the forum for that , but was just curious.BTW, the errors that you saw were on table...i.e. keys out of range. I have recreatd the table as this is used as a datawarehouse table...as we are not using SQL Analyssi service. |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-04-16 : 14:36:22
|
| I'm not a hardware person but I know there are diagnostics tools that ship with the server. You might find something in the event logs on the server too. Also, the errors appear to be indexes (IndexID is on the error lines) on the table. HTH.Terry |
|
|
|
cv_badri
Starting Member
1 Post |
Posted - 2010-06-10 : 11:07:43
|
| Restarted SQL Server Service and then retried, worked fine for me... |
|
|
|
|
Error: 7987, Severity: 22, State: 3