SQL Server 2008 R2 – DDL Statements Committed Within Transaction

sql-server-2008-r2

I am working on a script that involves a lot of structural changes, and like to return to a "base" condition while I work on what all needs to be changed, so I have my changes wrapped in a begin/rollback transaction.

This works just fine IF there are no errors, but when I make a mistake (attempting to drop a column that doesn't exist or which has a FK constraint, etc), the rollback does not work. So, objects that I have renamed with sp_rename or columns that I have dropped, have the wrong name/don't exist.

Assuming that this is documented/correct behavior, where is it documented? Probably just my eyes glazing over…but I can't recall/find anything that says a failure causes a commit.

Best Answer

SQL Server does not auto-commit an explicit transaction.

What might be happening in you case is that the transaction is aborted and rolled back, but your script continues, executing the remainder without a wrapping transaction. This is particularly likely if you have GO statements in your script.

To implement cross batch error handling you can do something like this:

BEGIN TRAN
 CREATE TABLE #OriginalTransaction(i INT);
 BEGIN TRY
   -- do stuff here
 END TRY
 BEGIN CATCH
   IF(@@TRANCOUNT>0)ROLLBACK;
 END CATCH;

GO
 IF(OBJECT_ID('tempdb..#OriginalTransaction') IS NOT NULL)
 BEGIN
  BEGIN TRY
    -- do more stuff here
  END TRY
  BEGIN CATCH
    IF(@@TRANCOUNT>0)ROLLBACK;
  END CATCH;
 END;

GO
 IF(OBJECT_ID('tempdb..#OriginalTransaction') IS NOT NULL)
 BEGIN
   COMMIT;
 END;

GO
 IF(@@TRANCOUNT>0)ROLLBACK;
GO
 IF(OBJECT_ID('tempdb..#OriginalTransaction') IS NOT NULL)
 BEGIN
   DROP TABLE #OriginalTransaction;
 END;
Related Question