clustered index got disabled suddenly

Question

I have a large database with size of 40 GB and a one of the main tables which have row count of 3763071 rows and name of "XXX" and that table has a clustered index which is the primary key

suddenly that clustered index got disabled and the queries stopped

and i have a huge concurrent access on that table And i have a full text index which got disabled too on the same table and when i opened the logs found that the sql got some updates just before that problem. I am using dot net framework 4.5 and entity framework as ORM and sql server 2014 .so i'm asking what could cause that problem to avoid it again?? and one another question i have more than 25 index on that table so is it bad or good to have that number of indexes which have repeated columns?

score 3 · Answer 1 · edited May 13 '20 at 18:26

I'm sure, by now, you've rebuilt your clustering index and your 25 non-clustered indexes as well. While it might be difficult to determine why your clustering index got disabled in the first place, you could try implementing one or more server DDL triggers that would fire after an ALTER INDEX command and interrogate the command for the word 'disable' and roll the transaction back - (and wait for someone or some process to complain about that action).

Example here:

--DDL trigger to prevent index disable
USE [master]
GO
alter TRIGGER [xx_TestDisable] 
ON ALL SERVER 
FOR ALTER_INDEX as

declare @text nvarchar(max)

SET @text = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 

if @text like '%disable%'
    rollback
GO

ENABLE TRIGGER [xx_TestDisable] ON ALL SERVER
GO

--Testing to see if the DDL trigger rolls back a disable
CREATE TABLE T (X INT CONSTRAINT IX_Index PRIMARY KEY);

ALTER INDEX IX_Index ON T DISABLE;

SELECT *
FROM T

As far as your 25 indexes, I think I'll borrow a quote that is attributed to Albert Einstein (paraphrased) - “Everything should be made as simple as possible, but no simpler.”

If you 'need' 25 indexes, then you 'need' 25 indexes.

Only you can decide which indexes should be created and maintained, but you should definitely use sys.dm_db_index_usage_stats to see how active your indexes are. Here is a good start.

score 3 · Answer 2 · edited May 13 '20 at 18:25

clustered index got disabled suddenly

You have to rebuild it to access the base table.

then Data in the table still exists, but will not be accessible for anything other than Drop or REBUILD operations. All related Non-clustered Indexes and views will be unavailable as well as Foreign Keys referencing the table will be disabled and there by leading the FAILURE for all the queries that are referencing the table.

Provided that your default trace has not rolled over, you can trace who disabled the index from default trace.

DECLARE @filename NVARCHAR(4000);

SELECT @filename = REVERSE(SUBSTRING(REVERSE([path]), 
     CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
   FROM sys.traces WHERE is_default = 1;

SELECT TextData, LoginName, StartTime, DatabaseID, ObjectID, IndexID
   FROM sys.fn_trace_gettable(@filename, DEFAULT)
   WHERE EventClass = 164
   AND DatabaseID > 4
   AND ObjectID IS NOT NULL
   AND IndexID IS NOT NULL
   ORDER BY StartTime DESC;

For a more robust solution, you need to create a server level trigger as described by Aaron in his blog post.

and one another question i have more than 25 index on that table so is it bad or good to have that number of indexes which have repeated columns?

Index has a maintenance cost to it. I would highly recommend to use sp_BlitzIndex (from Brent Ozar's team) to have a better view of your current index portfolio.

clustered index got disabled suddenly

2 Answers2