Poor transaction handling causes CU upgrade to fail
On every upgrade (CU, SP etc) the install process runs the script msdb110_upgrade.sql.
I have spotted a few places where the error handling is not good. Consider this passage:
BEGIN TRY EXEC sp_grantdbaccess @loginame = @owner_name END TRY BEGIN CATCH RAISERROR('A problem was encountered ...', 10, 127) WITH LOG END CATCH
The CATCH handler should include the line
IF @@trancount > 0 ROLLBACK TRANSACTION
sp_grantdbaccess, a remnant from SQL 2000, includes this passage:
exec (@stmtU) if @@error <> 0 begin ROLLBACK TRANSACTION return (1) end
But the ROLLBACK will of course never be executed, if the error is caught by the CATCH handler in the script.
Because the transaction is never rolled back, the script jogs on with the transaction. It later fails when it tries to change a configuration parameter, which is not permitted in a transaction.
Attached is an errorlog from an installation of SQL 2016 CU11 where this flaw caused the upgrade to fail. This was not from my own system, but I culled it from a thread in the MSDN forums. See https://social.msdn.microsoft.com/Forums/sqlserver/en-US/73e1d3b5-feb6-465e-a92b-b28e124cea02/sql-server-2016-sp2-cu11-patch-failed?forum=sqldatabaseengine for more details.