SQL Event Action ID and Class Type for Create and Drop Index showing Wrong

Question

SQL Server 2022

Tool : SQL Server Management Studio Version 19

Windows Version : 10

Hi Team,

Please help me on the below Query

Query: SQL Event Action ID and Class Type for Create and Drop Index shows AL (Expected Action ID=CR) and U (Expected class type=IX), Please help on why the action id is coming as AL instead of CR and Class type IX instead of U

Screenshots attached below

Create Index Action Id and Class type on Event Viewer

Drop Index Action Id and Class Type on Event Viewer

Thanks in Advance

Answer 1

Hi @Rathish S,

The Action ID and Class Type values in the Event Viewer for Create and Drop Index events are determined by the SQL Server engine and are based on the internal implementation of the CREATE INDEX and DROP INDEX statements.

The Action ID for a Create Index event is AL, which stands for "Altered Object." This is because creating an index involves altering the structure of the table or view on which the index is being created. The expected Action ID value of CR may not be used because the SQL Server engine does not treat the creation of an index as a new object creation.

The Class Type for a Create Index event is IX, which stands for "Index." The expected Class Type value of U (for "User-defined Table or View") may not be used because an index is not considered a user-defined table or view. Instead, it is considered a database object that is associated with a table or view.

Similarly, the Action ID for a Drop Index event is also AL, and the Class Type is IX.

Therefore, the values that you are seeing in the Event Viewer for Create and Drop Index events are expected based on the SQL Server engine's internal implementation.

By the way, you should focus more on the Error Level event here.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".