Jaa

Gotcha: When Server-Scoped DDL Triggers don’t honor ROLLBACK

  • Artikkeli

I’ve used DDL Triggers before to prevent database DROP DATABASE operations.  For example:

            CREATE TRIGGER ddl_ss_prevent_database_drop

ON ALL SERVER

FOR DROP_DATABASE

AS

PRINT 'Will not drop database until you disable this trigger.'

ROLLBACK

GO

If you try to drop a database with this DDL trigger in place, you’ll get the following message:

Will not drop database until you disable this trigger.

Msg 3609, Level 16, State 2, Line 2

The transaction ended in the trigger. The batch has been aborted.

What’s more – your DROP DATABASE truly is prevented, as one would expect.

But this behavior isn’t universal across all operations.  Sometimes ROLLBACK isn’t possible.  Take the following example of a DDL trigger on an ALTER_DATABASE event:

CREATE TRIGGER ddl_ss_tr_no_alter_db

ON ALL SERVER

FOR ALTER_DATABASE

AS

PRINT 'No ALTER DATABASE allowed!'

ROLLBACK TRAN;

GO

If you execute an ALTER DATABASE – you will indeed get an error message that looks like your operation was prevented – but this is not actually the case.  For example – let’s say I want to increase a file size:

ALTER DATABASE [MR1] MODIFY FILE ( NAME = N'MR1_2', SIZE = 70600KB )

This returns:

No ALTER DATABASE allowed!

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

But if I check the file size – sure enough – my ALTER DATABASE MODIFY FILE was actually executed and not aborted. 

Why?  Some DDL operations aren’t allowed to be rolled back in an atomic fashion in line with the DDL trigger.  For example – DROP DATABASE can be prevented, but not ALTER DATABASE.  At first I thought that the following BOL topic covers – in a roundabout way – what won’t be honored for rollbacks: “Transact-SQL Statements Allowed in Transactions” – however DROP DATABASE is on this list, so this isn’t consistent with what one sees with DDL triggers.

So my recommendation is as follows… If you are expecting a ROLLBACK for your DDL trigger – be sure to test that it actually rolls back the operation.  Don’t rely just on the message back to your client session.  You should actually verify that the operation was rolled back.

Comments

  • Anonymous
    June 03, 2010
    I ran into the same situation with the ALTER_DATABASE but in regards to the renaming of a DATABASE.  All signs showed that the renaming didn't take, but if you refresh the database list, lo 'n behold, the database was renamed!  I'm still working on a solution to this issue.  It involves capturing the new database name, verifying it against a daily backup of database names & if it doesn't match, it gets renamed back to the old database name.

  • Anonymous
    June 03, 2010
    Thanks John.  I wish that the behavior was more uniform, but looks like we'll have to just be wary of the scope.  Appreciate the comment.