Why CATCH block allows the transaction to commit in SQL Server 2012

Question

Recently, I encountered an SQL error due to the following statement in our legacy code. It tries to drop a temp table, and move on if it hasn't been defined. Clearly, it's a bad way to check the existence of a temp table, but that's not my question here.

BEGIN TRY 
    DROP TABLE #my_temp_table
END TRY 
BEGIN CATCH 

END CATCH 

The statement runs fine (without any error) as is, but as soon as you put it in a Begin Tran/Commit Tran block like the following, the behavior becomes interesting.

BEGIN TRAN
    BEGIN TRY 
        DROP TABLE #my_temp_table
    END TRY 
    BEGIN CATCH 

    END CATCH 
COMMIT TRAN

My understanding is that Try..Catch block doesn't affect Transactions - once it goes into the Catch block, the transaction will be in an uncommittable state, and the transaction will be rolled back, and that's what I see on SQL Server 2008 R2 (SP1) - 10.50.2550.0. When it's executed inside a Begin Tran/Commit Tran block, we will get an error:

Msg 3930, Level 16, State 1, Line 8
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

However, it runs without any error on SQL Server 2012 - 11.0.5058.0. The XACT_STATE() returns 1 after the END CATCH line. The transaction will be committed, and if there are other data changes before and after the DROP TABLE statement, the changes will stay.

BEGIN TRAN
    BEGIN TRY 
        DROP TABLE #my_temp_table
    END TRY 
    BEGIN CATCH 

    END CATCH 
    PRINT XACT_STATE()
COMMIT TRAN

In all these tests, I've made sure XACT_ABORT is OFF. So my question is what will cause this behavior difference. Is it truly a different between 2008 R2 and 2012, or it's some server/DB settings controlling how Try...Catch block and Transaction work.

---

Edit 1: I tried to run the following script on both 2008 R2 and 2012 instances. I also tried putting the INSERT dbo.UserOptionsLog line in different places, before the Begin Tran, after the Begin Try, after the Begin Catch, after the the commit tran, but it doesn't change the results.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'UserOptionsLog')
    CREATE TABLE dbo.UserOptionsLog([Set Option] SYSNAME, [Value] VARCHAR(100), ID INT IDENTITY NOT NULL)


BEGIN TRAN
    DELETE FROM dbo.UserOptionsLog
    INSERT dbo.UserOptionsLog EXEC('DBCC USEROPTIONS')
    SELECT * FROM dbo.UserOptionsLog

    BEGIN TRY 

        DROP TABLE #my_temp_table
    END TRY 
    BEGIN CATCH 

    END CATCH 
COMMIT TRAN

Results from 2008 R2 instance.

Set Option  Value   ID
textsize    2147483647  40
language    us_english  41
dateformat  mdy 42
datefirst   7   43
lock_timeout    -1  44
quoted_identifier   SET 45
arithabort  SET 46
ansi_null_dflt_on   SET 47
ansi_warnings   SET 48
ansi_padding    SET 49
ansi_nulls  SET 50
concat_null_yields_null SET 51
isolation level read committed  52

Messages from 2008 R2 instance

(0 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(13 row(s) affected)

(13 row(s) affected)
Msg 3930, Level 16, State 1, Line 18
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Results from 2012 instance.

Set Option  Value   ID
textsize    2147483647  53
language    us_english  54
dateformat  mdy 55
datefirst   7   56
lock_timeout    -1  57
quoted_identifier   SET 58
arithabort  SET 59
ansi_null_dflt_on   SET 60
ansi_warnings   SET 61
ansi_padding    SET 62
ansi_nulls  SET 63
concat_null_yields_null SET 64
isolation level read committed  65

Messages from 2012 instance.

(13 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(13 row(s) affected)

(13 row(s) affected)

Answer 1

[...]and that's what I see on SQL Server 2008 R2 (SP1) - 10.50.2550.0. When it's executed inside a Begin Tran/Commit Tran block, we will get an error:

Msg 3930, Level 16, State 1, Line 8 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Msg 3998, Level 16, State 1, Line 1 Uncommittable transaction is detected at the end of the batch. The transaction is rolled back. However, it runs without any error on SQL Server 2012 - 11.0.5058.0. [...]

I believe the reason for this diff. behavior is the value of XACT_ABORT setting.

When OFF then XACT_STATE() returns 1, TX is commitable and COMMIT TRAN is executing without errors:

SET XACT_ABORT OFF
BEGIN TRAN
    BEGIN TRY 
        DROP TABLE #my_temp_table
    END TRY 
    BEGIN CATCH 

    END CATCH 
    PRINT XACT_STATE()
COMMIT TRAN

but when is ON

SET XACT_ABORT ON
BEGIN TRAN
    BEGIN TRY 
        DROP TABLE #my_temp_table
    END TRY 
    BEGIN CATCH 

    END CATCH 
    PRINT XACT_STATE()
COMMIT TRAN

because of error/exception intercepted by CATCH block, TX becomes domed/uncommitable (-1) and COMMIT TRAN raise another error/exception:

-1
Msg 3930, Level 16, State 1, Line 10
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Update:

I reproduced reported behaviour on SQL2008R2. On 2008R2, it seems that no matter what value has XACT_ABORT (ON/OFF), TX becomes uncommitable. SQL2012 changed this behaviour thus: TX becomes uncommitable only when XACT_ABORT is ON.