SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Index Disabled and Index Rebuild

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.

Read More

Top 5 SQL Server DMV for Index Analysis - SQL Server DBA

SQL Server - Predict ALTER INDEX REORGANIZE finish time

SQL SERVER REBUILD INDEX


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer