Trigger Blocks Index Maintenance: How to Let Rebuilds Pass

A trigger blocks index maintenance when it watches every index event, because a rebuild is an index event too. A small change to the trigger lets maintenance through and keeps the guard.

Why a Rebuild Fails Under the Guard

The earlier post Prevent Index Changes in SQL Server With a DDL Trigger guards a database with one trigger. The trigger is blunt. It disables index operations for every user, and a rebuild is an index operation. This is the main reason I prefer database roles over triggers.

The demo database is named RebuildGuardDemo. It holds an employee table with 20,000 rows and one nonclustered index.

IF DB_ID(N'RebuildGuardDemo') IS NULL CREATE DATABASE RebuildGuardDemo;
GO
USE RebuildGuardDemo;
GO
DROP TABLE IF EXISTS dbo.Employees;
CREATE TABLE dbo.Employees (EmployeeID int IDENTITY(1,1) CONSTRAINT PK_Employees PRIMARY KEY, LastName nvarchar(50) NOT NULL, Department nvarchar(30) NOT NULL);
INSERT INTO dbo.Employees (LastName, Department) SELECT CONCAT(N'Name', value), CONCAT(N'Dept', value % 20) FROM GENERATE_SERIES(1, 20000);
CREATE INDEX IX_Employees_Department ON dbo.Employees (Department);

The Trigger That Stops Maintenance

This version prints a message and rolls back. It watches the three index events, so it also catches ALTER INDEX.

CREATE OR ALTER TRIGGER trg_StopIndexChanges ON DATABASE
FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
    PRINT N'Index changes are not allowed here.';
    ROLLBACK TRANSACTION;

Now run the weekly maintenance statement. A rebuild rewrites the index and removes fragmentation. It is the job that keeps a busy database healthy.

ALTER INDEX IX_Employees_Department ON dbo.Employees REBUILD;

Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

The rebuild fails. The same happens to REORGANIZE. A nightly job that rebuilds or reorganizes indexes fails the same way.

Find the Trigger That Blocks Maintenance

Msg 3609 does not name the trigger, so the cause is easy to miss. When an index job fails with Msg 3609, list the triggers of the database. The query below shows each trigger and whether it is disabled. It also shows the events the trigger watches and whether its code contains a ROLLBACK.

SELECT t.name AS TriggerName, t.is_disabled AS IsDisabled,
       STRING_AGG(e.type_desc, N', ') WITHIN GROUP (ORDER BY e.type_desc) AS Events,
       CASE WHEN m.definition LIKE N'%ROLLBACK%' THEN N'Yes' ELSE N'No' END AS RollsBack
FROM sys.triggers AS t
JOIN sys.trigger_events AS e ON e.object_id = t.object_id
JOIN sys.sql_modules AS m ON m.object_id = t.object_id
WHERE t.parent_class = 0
GROUP BY t.name, t.is_disabled, m.definition;

A row with ALTER_INDEX and a ROLLBACK is the suspect, because that trigger blocks index maintenance. Server-level triggers live in a different view, sys.server_triggers, so check that view too when the database has none. The documentation says a server scoped DDL trigger can fire on these events too. Check both before you change anything, and write down what each trigger does and who asked for it.

A Trigger That Lets Rebuilds Pass

A DDL trigger can read what the statement was. The function EVENTDATA returns an XML document with the event type and the text of the command. The new trigger reads both. It lets an ALTER INDEX through when the command is a rebuild or a reorganize and not a disable. Everything else is rolled back with a message that explains the rule.

CREATE OR ALTER TRIGGER trg_StopIndexChanges ON DATABASE
FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
BEGIN
    DECLARE @event xml = EVENTDATA();
    DECLARE @type nvarchar(100) = @event.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)');
    DECLARE @command nvarchar(max) = @event.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');
    IF @type = N'ALTER_INDEX'
       AND (@command LIKE N'%REBUILD%' OR @command LIKE N'%REORGANIZE%')
       AND @command NOT LIKE N'%DISABLE%'
        RETURN;
    ROLLBACK TRANSACTION;
    THROW 50001, N'Index changes are blocked. Only REBUILD and REORGANIZE are allowed.', 1;
END;

Run the maintenance statements again. The first rebuilds one index, the second reorganizes it, and the third rebuilds every index on the table. None of them returns an error.

ALTER INDEX IX_Employees_Department ON dbo.Employees REBUILD;
GO
ALTER INDEX IX_Employees_Department ON dbo.Employees REORGANIZE;
GO
ALTER INDEX ALL ON dbo.Employees REBUILD;

Now confirm that the guard still works. Creating, dropping and disabling an index must fail.

CREATE INDEX IX_Employees_LastName ON dbo.Employees (LastName);
GO
DROP INDEX IX_Employees_Department ON dbo.Employees;
GO
ALTER INDEX IX_Employees_Department ON dbo.Employees DISABLE;

Msg 50001, Level 16, State 1, Procedure trg_StopIndexChanges, Line 13
Index changes are blocked. Only REBUILD and REORGANIZE are allowed.

Each of the three statements returns that same Msg 50001. The index still exists and is enabled. Maintenance passes, and structure changes do not.

The Gap in This Version

The trigger reads the command text, and text is easy to fool. A rebuild can carry options that change the index. The next statement is a rebuild by its words, and it changes the compression of the index.

ALTER INDEX IX_Employees_Department ON dbo.Employees REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
SELECT i.name, i.is_disabled, p.data_compression_desc AS Compression
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.Employees')
ORDER BY i.index_id;

The statement went through, and the index is now page compressed. Text matching is a convenience, not a security control. The match reads the index name too. A rebuild of an index named IX_Disable_Flag is blocked, because its text contains DISABLE. If the rule must hold, remove the permission to change indexes from the people it applies to. A named maintenance login is another option, because the trigger can compare the caller’s login with an allowed name.

Is This Worth the Trouble?

You could argue that you should drop the trigger and trust the team. That is the right answer when the team is small. The trigger earns its place when many people can change the schema and a rule helps. Keep the maintenance exception, test it after every change to the trigger, and read the failed jobs in the morning.

What to Remember

A trigger blocks index maintenance as soon as it watches ALTER_INDEX without looking at the command. Read EVENTDATA, allow REBUILD and REORGANIZE, and keep blocking create, drop and disable. Test the nightly job against the trigger before it goes live.

When you finish, drop the demo database.

USE master;
GO
ALTER DATABASE RebuildGuardDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE RebuildGuardDemo;

A trigger that blocks maintenance is not a guard, it is a job waiting to fail.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.