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)