Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Index Disabled and Index Rebuild

I received the following message from a Developer. The message was on a Test SQL Server

 NC_myIndex " on table "VeryLargeTable" (specified in the FROM clause) is disabled or resides in a filegroup which is not online

 The cause of the message was that the index was disabled.  I discovered the ETL guy was testing speeding up the UPDATE process of a very large dataset.

The instructions I’d sent were to Disable Index and Rebuild Index for Updates on very large tables

 

----Disable Index 
ALTER INDEX [NC_myindex] ON [dbo].[VeryLargeTable] DISABLE
GO
----Enable Index - 
ALTER INDEX [NC_myIndex] ON [dbo].[VeryLargeTable] REBUILD  WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

 

He’d forgotten to Enable the Index.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.