Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

SQL Server 2016 SP2-CU3 patch breaks if replication is enabled.

SQL Server 2016 SP2-CU3 patch breaks if replication is enabled.

When trying to install KB4458871, with having replication turned on, the install will fail with the error: The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]). 2018-10-23 12:15:07.03 spid5s Error executing sp_vupgrade_replication. I have found the following workaround to manually install the patch. Stop SQLAgent, Disable SQLAgent, Set StartUp TraceFlag -T902. Restart MSSQLService. Once the service restarts, install the Patch, which should succeed successfully due to trace flag. Once successful install, you must execute sp_vupgrade_replication manually. Once that succeeds, you can remove the Trace Flag, restart MSSQLService and once that starts up successfully, you can go ahead and set MSSQLAgent to Automatic and then Enable SQLAgent.

4 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Jason Squires shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

9 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • Jubaco30 commented  ·   ·  Flag as inappropriate

    Excelent Work!, we have this problem with CU4 , we renamed account "sa" to another name, when renaming back to original name "sa" it worked fine!!

    Thank you all!

  • Joby Mathew commented  ·   ·  Flag as inappropriate

    We encountered the error while do the CU#4 on SQL server 2016 SP2 Cluster instance. After CU#4 patch the Instance could not start.
    The transactional replication also enabled the instance.
    The error logged in the logs are,

    2018-12-18 04:36:55.620 Server Software Usage Metrics is enabled.
    2018-12-18 04:36:56.040 spid6s Attempting to copy article resolvers from SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.SQL1\Replication\ArticleResolver
    2018-12-18 04:36:59.570 spid6s The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]).
    2018-12-18 04:36:59.570 spid6s Error executing sp_vupgrade_replication.
    2018-12-18 04:36:59.570 spid6s Saving upgrade script status to 'SOFTWARE\Microsoft\MSSQLServer\Replication\Setup'.
    2018-12-18 04:36:59.570 spid6s Saved upgrade script status successfully.
    2018-12-18 04:36:59.570 spid6s Database 'master' is upgrading script 'upgrade_ucp_cmdw_discovery.sql' from level 218108834 to level 218109041.
    2018-12-18 04:36:59.800 spid6s Database 'master' is upgrading script 'msdb110_upgrade.sql' from level 218108834 to level 218109041.
    2018-12-18 04:36:59.800 spid6s ----------------------------------
    2018-12-18 04:36:59.800 spid6s Starting execution of PRE_MSDB.SQL
    2018-12-18 04:36:59.800 spid6s ----------------------------------
    2018-12-18 04:36:59.890 spid6s Error: 15002, Severity: 16, State: 1.
    2018-12-18 04:36:59.890 spid6s The procedure 'sys.sp_dbcmptlevel' cannot be executed within a transaction.
    2018-12-18 04:36:59.890 spid6s -----------------------------------------
    2018-12-18 04:36:59.890 spid6s Starting execution of PRE_SQLAGENT100.SQL
    2018-12-18 04:36:59.890 spid6s -----------------------------------------
    2018-12-18 04:36:59.900 spid6s Error: 226, Severity: 16, State: 6.
    2018-12-18 04:36:59.900 spid6s ALTER DATABASE statement not allowed within multi-statement transaction.
    2018-12-18 04:36:59.900 spid6s Error: 912, Severity: 21, State: 2.
    2018-12-18 04:36:59.900 spid6s Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 226, state 6, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
    2018-12-18 04:36:59.900 spid6s Error: 3417, Severity: 21, State: 3.
    2018-12-18 04:36:59.900 spid6s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
    2018-12-18 04:36:59.900 spid6s SQL Server shutdown has been initiated

    The workaround- execute the sp: sp_vupgrade_replication manually works. Thanks a lots for the workaround

  • Anonymous commented  ·   ·  Flag as inappropriate

    All 3 of you: thanks for sharing this! I just had this happen too, and had a severe pucker moment while I tried to work it out. This post came to the rescue!

    Jason, thanks for talking about just renaming SA, because I wasn't sure what I'd need to do to get it back to a happy state, since we'd already run the patch, and wasn't sure what I needed to do to exec sp_vupgrade_replication manually.

    *pant* *pant* Holy ****. Just.... seriously, Microsoft?!

  • Jason Squires commented  ·   ·  Flag as inappropriate

    Peter, I just did the same today. I renamed the sysadmin account back to sa and upgrade succeeded without an issue. This definitely needs to be fixed I would think. There is no reason why we should have to rename our already renamed sa account back to the original sa. Thanks for posting your results and solution as well!!!

  • Peter Guerndt commented  ·   ·  Flag as inappropriate

    It seems like this is happening because we renamed the [sa] account.

    The script sp_vupgrade_replication is adding a new agent job [Monitor and sync replication agent jobs]. When the [sa] account is renamed it tries to add the job with owner [domain\sa] which doesn't exist in the domain and thus we receive the error:
    "The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]). " and the script fails.

    When we use the default name [sa] installing SP2CU4 is working without any problems and the new job is created with [sa] as owner.

  • Peter Guerndt commented  ·   ·  Flag as inappropriate

    Hi,
    We experienced the same errror when installing SQL 2016 SP2-CU4 on an instance with replication enabled. So this seems to be an issue there too. Thanks so much for the workaround it worked and saved us tonight.

    We received the same error:
    2018-11-29 20:46:09.47 spid3s The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]).
    2018-11-29 20:46:09.47 spid3s Error executing sp_vupgrade_replication.
    2018-11-29 20:46:09.47 spid3s Saving upgrade script status to 'SOFTWARE\Microsoft\MSSQLServer\Replication\Setup'.
    2018-11-29 20:46:09.47 spid3s Saved upgrade script status successfully.
    2018-11-29 20:46:09.47 spid3s Database 'master' is upgrading script 'upgrade_ucp_cmdw_discovery.sql' from level 218108834 to level 218109041.
    2018-11-29 20:46:09.69 spid3s Database 'master' is upgrading script 'msdb110_upgrade.sql' from level 218108834 to level 218109041.
    2018-11-29 20:46:09.69 spid3s ----------------------------------
    2018-11-29 20:46:09.69 spid3s Starting execution of PRE_MSDB.SQL
    2018-11-29 20:46:09.69 spid3s ----------------------------------
    2018-11-29 20:46:09.76 spid3s Error: 15002, Severity: 16, State: 1.
    2018-11-29 20:46:09.76 spid3s The procedure 'sys.sp_dbcmptlevel' cannot be executed within a transaction.
    2018-11-29 20:46:09.76 spid3s -----------------------------------------
    2018-11-29 20:46:09.76 spid3s Starting execution of PRE_SQLAGENT100.SQL
    2018-11-29 20:46:09.76 spid3s -----------------------------------------
    2018-11-29 20:46:09.77 spid3s Error: 226, Severity: 16, State: 6.
    2018-11-29 20:46:09.77 spid3s ALTER DATABASE statement not allowed within multi-statement transaction.
    2018-11-29 20:46:09.77 spid3s The failed batch of t-sql statements :

    --set compatibility level for msdb database to 130
    ALTER DATABASE msdb
    SET COMPATIBILITY_LEVEL = 130

    2018-11-29 20:46:09.77 spid3s Error: 912, Severity: 21, State: 2.
    2018-11-29 20:46:09.77 spid3s Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 226, state 6, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
    2018-11-29 20:46:09.77 spid3s Error: 3417, Severity: 21, State: 3.
    2018-11-29 20:46:09.77 spid3s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
    2018-11-29 20:46:09.77 spid3s SQL Server shutdown has been initiated
    2018-11-29 20:46:09.79 spid3s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Feedback and Knowledge Base