SQL SERVER – Upgrade Error – ALTER DATABASE Statement Not Allowed Within Multi-statement Transaction

Most of the time applying service pack for SQL Server is a child’s play. Double click on exe, keep hitting next, next and finally press update. But when it fails, you would need an expert to fix the issue. Recently, one of my clients faced a problem while installing SQL Server 2014 Sp2. The Service Pack error failed at the final stage. And now the SQL Server services were not starting. This issue can have various variations where SQL Services doesn’t start after patching. Few on my earlier blogs on the same topic are listed below. Let us learn about ALTER DATABASE Statement Not Allowed Within Multi-statement Transaction.

After looking at all the log files we narrowed the issue down to the below section in SQL Server Errorlog. Based on the errors I saw here and based on my previous experiences with such errors, I realized that this is a Script Upgrade failure. Below are the steps which occur during a patching process,

  • Whenever any SQL Server patch is applied, the setup would patch the binaries first.
  • During the restart of the instance, SQL Server startup would go through “script upgrade mode” during the recovery
  • Script upgrade mode is the phase where objects inside the databases are upgraded based on recent patch applied.
  • Based on features installed and a number of databases available, it would take a varying amount of time.

2018-03-11 17:54:38.98 spid9s Upgrading subscription settings and system objects in database [TransRepl].
2018-03-11 17:54:39.12 spid9s Invalid object name ‘MSreplication_objects’.
2018-03-11 17:54:39.12 spid9s Error executing sp_vupgrade_replication.
2018-03-11 17:54:39.12 spid9s Saving upgrade script status to ‘SOFTWARE\Microsoft\MSSQLServer\Replication\Setup’.
2018-03-11 17:54:39.12 spid9s Saved upgrade script status successfully.
2018-03-11 17:54:39.12 spid9s Database ‘master’ is upgrading script ‘upgrade_ucp_cmdw_discovery.sql’ from level 201330692 to level 201331592.
2018-03-11 17:54:39.40 spid9s Database ‘master’ is upgrading script ‘msdb110_upgrade.sql’ from level 201330692 to level 201331592.
2018-03-11 17:54:39.40 spid9s ———————————-
2018-03-11 17:54:39.40 spid9s Starting execution of PRE_MSDB.SQL
2018-03-11 17:54:39.40 spid9s ———————————-
2018-03-11 17:54:39.64 spid9s Error: 15002, Severity: 16, State: 1.
2018-03-11 17:54:39.64 spid9s The procedure ‘sys.sp_dbcmptlevel’ cannot be executed within a transaction.
2018-03-11 17:54:39.64 spid9s —————————————–
2018-03-11 17:54:39.64 spid9s Starting execution of PRE_SQLAGENT100.SQL
2018-03-11 17:54:39.64 spid9s —————————————–
2018-03-11 17:54:39.65 spid9s Error: 226, Severity: 16, State: 6.
2018-03-11 17:54:39.65 spid9s ALTER DATABASE statement not allowed within multi-statement transaction.

From above we can see that we hit an error while executing the Stored Proc – sp_vupgrade_replication And it also said that it was not able to find the object – MSreplication_objects

As per Microsoft documentation sp_vupgrade_replication used for below.

Activated by setup when upgrading a replication server. Upgrades schema and system data as needed to support replication at the current product level. Creates new replication system objects in system and user databases. This stored procedure is executed at the machine where the replication upgrade is to occur.

Solarwinds

WORKAROUND/SOLUTION

As per my previous articles on the same topic, I knew that I need to use trace flag 902 to bypass script upgrade mode and fix the real cause of the error. I started SQL using trace flag 902 as below

NET START MSSQL$SQL2014 /T902

Refer: SQL SERVER – 2005 – Start Stop Restart SQL Server from Command Prompt

I was able to connect to SQL Server because the problem script didn’t run due to trace flag.

Then I straight went to the database in question and expanded the System Tables section could see that the table – MSreplication_objects was missing. Now how do we get back this replication related system table? To quickly confirm this fact, we checked for the same in the other databases which were a part of replication and confirmed that this table needs to be present.

I then got an idea to script out this stored proc from the other database and create it here. I went ahead and did the same.

SQL SERVER - Upgrade Error - ALTER DATABASE Statement Not Allowed Within Multi-statement Transaction patch-repl-01

The extracted script looks like below,

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MSreplication_objects](
	[publisher] [sysname] NULL,
	[publisher_db] [sysname] NULL,
	[publication] [sysname] NULL,
	[object_name] [sysname] NOT NULL,
	[object_type] [char](2) NOT NULL,
	[article] [sysname] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

I was happy to see the script execute successfully. But to my surprise, the stored procedure got created as a user stored proc instead of a system stored proc.

SQL SERVER - Upgrade Error - ALTER DATABASE Statement Not Allowed Within Multi-statement Transaction patch-repl-02

Then to promote this stored proc as a system proc I used an undocumented stored procedure sp_MS_marksystemobject

exec sys.sp_MS_marksystemobject  MSreplication_objects

After that, we can see that the table moved into system tables folder.

SQL SERVER - Upgrade Error - ALTER DATABASE Statement Not Allowed Within Multi-statement Transaction patch-repl-03

Once this was done then, I stopped SQL and started it usually (without trace flag 902), and it was able to start successfully. Have you encountered any other flavor of script upgrade mode?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , , ,
Previous Post
SQL SERVER – AlwaysOn Automatic Seeding Failure – Failure_code 15 and Failure_message: VDI Client Failed
Next Post
The Evolution of the DBA – Challenges, Changes and Upcoming Trends

Related Posts

2 Comments. Leave new

  • 2019-01-20 16:46:22.28 spid4s Error executing sp_vupgrade_replication.
    2019-01-20 16:46:22.28 spid4s Saving upgrade script status to ‘SOFTWARE\Microsoft\MSSQLServer\Replication\Setup’.
    2019-01-20 16:46:22.28 spid4s Saved upgrade script status successfully.
    2019-01-20 16:46:22.28 spid4s Database ‘master’ is upgrading script ‘upgrade_ucp_cmdw_discovery.sql’ from level 218108834 to level 218109041.
    2019-01-20 16:46:22.56 spid4s Database ‘master’ is upgrading script ‘msdb110_upgrade.sql’ from level 218108834 to level 218109041.
    2019-01-20 16:46:22.56 spid4s ———————————-
    2019-01-20 16:46:22.56 spid4s Starting execution of PRE_MSDB.SQL
    2019-01-20 16:46:22.56 spid4s ———————————-
    2019-01-20 16:46:22.68 spid4s Error: 15002, Severity: 16, State: 1.
    2019-01-20 16:46:22.68 spid4s The procedure ‘sys.sp_dbcmptlevel’ cannot be executed within a transaction.
    2019-01-20 16:46:22.68 spid4s —————————————–
    2019-01-20 16:46:22.68 spid4s Starting execution of PRE_SQLAGENT100.SQL
    2019-01-20 16:46:22.68 spid4s —————————————–
    2019-01-20 16:46:22.69 spid4s Error: 226, Severity: 16, State: 6.
    2019-01-20 16:46:22.69 spid4s ALTER DATABASE statement not allowed within multi-statement transaction.
    2019-01-20 16:46:22.69 spid4s The failed batch of t-sql statements :

    –set compatibility level for msdb database to 130
    ALTER DATABASE msdb
    SET COMPATIBILITY_LEVEL = 130

    2019-01-20 16:46:22.69 spid4s Error: 912, Severity: 21, State: 2.
    2019-01-20 16:46:22.69 spid4s Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 226, state 6, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
    2019-01-20 16:46:22.70 spid4s Error: 3417, Severity: 21, State: 3.
    2019-01-20 16:46:22.70 spid4s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
    2019-01-20 16:46:22.70 spid4s SQL Server shutdown has been initiated
    2019-01-20 16:46:22.70 spid4s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

    Any solution to above issue.

    Reply
  • Thanks, you saved my weekend. A deleted replication left some replication system tables (Thanks Microsoft :-() and the upgrade failed. Deleting the left replication tables solved the problem.

    Reply

Leave a Reply

Menu