drop/create index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

drop/create index

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-02-16 : 15:09:20

Hi, all,
I want to re-create an index on a production table.
I got an error 644 "could not find index entry...".
The DBCC CHECKDB and CHECKTABLE gave me this:

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 37575172, index ID 6: Page (1:939782) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 37575172, index ID 6, page (1:939782). Test (*(((int*) &m_reservedB) + i) == 0) failed. Values are 7 and 36.
DBCC results for 'Mfg_DFSFNSF'.
There are 1142314 rows in 326143 pages for object 'Mfg_DFSFNSF'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Mfg_DFSFNSF' (object ID 37575172).

The table script for the index is like this:

CREATE UNIQUE INDEX [Mfg_ITMDH_MbrIdx] ON [dbo].[Mfg_DFSFNSF]([_ITMDH_OwnRow], [_ITMDH_MbrKey], [RECTYPE]) WITH FILLFACTOR = 70 ON [PRIMARY]

My question is that can I drop it and run above create it to fix the problem in live mode?

I know the other option will be:
DBCC CHECKTABLE (FSDBMR.dbo.Mfg_DFSFNSF, repair_allow_data_loss)
But that has to put the db under single user mode.

Thanks!

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-02-16 : 15:22:31

This is just a non-clustered index so you don't need to run repair at all - you can rebuild the index. If you did need to fix this, repair isn't the best option anyway - do you have backups?

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-02-16 : 15:34:04

Yes, we do have daily complete backup and every 30 minute tlog backups.

So I will just drop the index and create it to get rid of the problem.

Great! Thanks a lot!

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-02-16 : 15:41:22

Be careful - if you drop and recreate the index then in the time between dropping and recreating something could happen that violates the uniqueness constraint that the index is enforcing. Better to rebuild the index or do a create with drop_existing.

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-02-16 : 17:08:47

So, in the following way? Thanks!

CREATE UNIQUE INDEX [Mfg_ITMDH_MbrIdx] ON [dbo].[Mfg_DFSFNSF]([_ITMDH_OwnRow],
[_ITMDH_MbrKey], [RECTYPE]) WITH FILLFACTOR = 70, drop_existing ON [PRIMARY]

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-02-16 : 17:24:39

or this?
DBCC DBREINDEX
( 'FSDBMR.dbo.Mfg_DFSFNSF' , Mfg_ITMDH_MbrIdx, 70 ) WITH NO_INFOMSGS

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-02-16 : 17:33:29

Yup

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources