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.
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
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)