25 August,2011 by Jack Vamvas
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
Conclusion : The DBA forgot to Enable the Index.