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.