http://www.sqlservercentral.com/blogs/sql_server_dba/2011/08/25/index-disabled--and-index-rebuild/

Printed 2014/08/27 07:28PM

Index Disabled and Index Rebuild

By Jack Vamvas, 2011/08/25

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.