SQL SERVER – Upgrade Error – Upgrading Subscription Settings and System Objects in Database [DBName].

There are many causes of SQL server patching failure. Looking at right log and finding the right cause is the key to solve it in time. In this blog, we would learn about an upgrade error which comes during “Upgrading subscription settings and system objects in database.”

One of my blog reader reached out to me via email. I offered them my incredibly famous On Demand assistance and I got started working in 10 minutes via GoToMeeting.

When I checked ERRORLOG, I found below.

2018-04-19 14:16:38.98 spid9s Upgrading subscription settings and system objects in database [BAPDB].
2018-04-19 14:16:39.12 spid9s Invalid object name ‘MSreplication_subscriptions’.
2018-04-19 14:16:39.12 spid9s Error executing sp_vupgrade_replication.
2018-04-19 14:16:39.12 spid9s Saving upgrade script status to ‘SOFTWARE\Microsoft\MSSQLServer\Replication\Setup’.
2018-04-19 14:16:39.12 spid9s Saved upgrade script status successfully.
2018-04-19 14:16:39.12 spid9s Database ‘master’ is upgrading script ‘upgrade_ucp_cmdw_discovery.sql’ from level 201330692 to level 201331592.
2018-04-19 14:16:39.40 spid9s Database ‘master’ is upgrading script ‘msdb110_upgrade.sql’ from level 201330692 to level 201331592.

WORKAROUND/SOLUTION

I Informed them they I already have a blog with exact same error SQL SERVER – Upgrade Error – ALTER DATABASE Statement Not Allowed Within Multi-statement Transaction

But they informed that they don’t have any replication as of now and they don’t want to create any system table using the method which I explained in my blog. I thought there could be a chance that some replication metadata was still left over, and script upgrade process thinks replication still persists in this database and was expecting this table. Based on their decision, I started SQL with trace flag 902.

NET START MSSQLSQLSERVER /T902

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

We used the system stored procedure (sp_removedbreplication) to remove replication related config from this database (this is there in ERRORLOG file)

USE BAPDB
GO
sp_removedbreplication 
GO

Once this was done then, I stopped SQL and started it normally (without trace flag 902) and it was able to start successfully.

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