2016-05-25

Alter Database Oddities

Many of you may already know that for SQL Server, an ALTER DATABASE command is not a transacted operation. As MSDN tells us: "The ALTER DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction."

I figured this out myself once upon a time. But not from reading documentation. It was after I created a server-level DDL trigger, much like this:

CREATE TRIGGER trgAlterDatabase 
ON ALL SERVER 
FOR ALTER_DATABASE 
AS 
DECLARE @TsqlCmd NVARCHAR(MAX) = 
    EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)')

IF @TsqlCmd COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%SET RECOVERY%'
BEGIN
    ROLLBACK;    --This doesn't work.
    DECLARE @ErrMsg NVARCHAR(MAX) = 'Please don''t change the RECOVERY model. ' +
    'This makes the DBA unhappy.' + 
        CHAR(13) + CHAR(10) +
        'If you need assistance, please contact TheDBA@YourOrg.com' 
    RAISERROR(@ErrMsg, 16, 1) WITH LOG;
END

If you create that trigger and subsequently change the recovery model, you'll get an error. But the recovery model doesn't get changed back to its original setting. As the TSql comment notes, ROLLBACK doesn't work in this context. And that's a shame too. Sometimes the recovery model gets changed on accident, by a third-party vendor's software installation, or by someone that isn't familiar with the ramifications. I was trying to prevent that. <Grumble> At least I knew it wasn't allowed. I just didn't know why. After seeing this Connect item, "why" didn't matter.

And yet, if I try to change the recovery model of a database that's in an Availability Group, or is part of a database mirroring session, I get an error similar to this:

Msg 1468, Level 16, State 1, Line 2
The operation cannot be performed on database "%.*ls" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

If ALTER DATABASE isn't a transacted operation, shouldn't the recovery model change, in spite of the error? (It does not.) What am I supposed to think here? The ability to prevent changing the recovery model (and maybe any ALTER DATABASE command) clearly exists. I want that ability too.


SHARE