![SQL SERVER - Upgrade Error - Upgrading Subscription Settings and System Objects in Database [DBName]. xerror](https://blog.sqlauthority.com/wp-content/uploads/2018/05/xerror.gif "SQL SERVER - Upgrade Error - Upgrading Subscription Settings and System Objects in Database [DBName]. xerror")
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)
1 Comment. Leave new
I had a similar problem when applying CU8 for SQL 2016 SP2.
The CU produced the error messages “Invalid object name ‘MSreplication_subscriptions’” “Error executing sp_vupgrade_replication” for a database that is not involved in replication.
Then the CU update hung. After more than an hour with no activity I killed the update.
I started SQL with trace flag T902.
Executing sp_removedbreplication did not fix the issue for me.
I detached the offending database as I had a copy elsewhere.
Then I stopped SQL and restarted it normally.
It started successfully and now displays “SQL Server 13.0.5426.0” in SSMS, which seems to indicate that the CU completed successfully.
However there is a folder left behind on the server drive where the database logfiles are kept. It’s named bb284dce9532b41ed5042074 and contains the file SCENARIOENGINE.EXE plus 78 dll files (e.g. MICROSOFT.SQLSERVER.CONFIGURATION.BOOTSTRAPEXTENSION.DLL)
Hotfix 5426 appears in the Installed Updates list on the server.
Was the install of the CU successful or not?
Can I simply delete the bb284dce9532b41ed5042074 folder?
Or do I need to back out the CU8 install and reinstall it?
I appreciate any guidance provided.