Sql-server – clustered index got disabled suddenly

clustered-indexsql server 2014

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?

Best Answer

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.

Related Question