Why would SQL Server automatically remove manually created indexes?

What causes Microsoft SQL Server to remove manually created indexes automatically? Every index I create it removes within a few hours.

Here is the code I use to create the indexes. Affected views run much faster. The index disappears / is removed within a few hours. The affected views run impossibly slow or fail.

CREATE NONCLUSTERED INDEX IX_Tablename_fieldname 
ON dbo.Tablename (fieldname) 
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

SQL Server does not automatically* remove indexes - neither does automatically execute any other DDL statement that alters the structure of your tables, views, functions, indexes, etc.

If your indexes are truly dropped, then some user or application or job is doing it.

*: SQL Server on Azure can automatically remove indexes if the option is enabled. it does so if they are duplicates or have been unused for 6 months or so. Not on version 2008 though or any other desktop version.

To find out who or what, you have various options:

• write a DDL trigger to catch the DROP INDEX statements (and even disallow them from deleting your index). See Ken Kim's answer.

• SQL Trace

• enable Audit

• use XEvents (Extended Events)

• use SQL Profiler

• read the transaction log

StackExchange Database Administrators Q#204923, answer score: 18