After upgrading installing Cumulative Update 13 for Microsoft SQL Server 2019 (KB5005679), the server failed to start. Error logs showed that this was because the upgrade script msdb110_upgrade.sql attempts to run stored procedure 'sys.sp_MSIsContainedAGSession'. However, the server has no such stored procedure, nor do our other, non-upgraded instances have it.
Error: 2812, Severity: 16, State: 62.
Could not find stored procedure 'sys.sp_MSIsContainedAGSession'.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 2812, state 62, 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.
Error: 3417, Severity: 21, State: 3.
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.
We've been able to get the server running by starting it with the trace flag T902, preventing the upgrade script from running, leaving it in a partially-upgraded state, but would like to get the issue fully corrected.
Searches for 'sys.sp_MSIsContainedAGSession' turned up no results related to the procedure. How might we create / recover this procedure, or is there a better approach to resolving this issue?
November 16, 2021 at 3:45 pm
I would start by reaching out to Microsoft for support.
They may suggest that you rebuild master, but you will lose all of your logins and roles and other objects stored in master. Or they may have a script you can run to either modify "msdb110_upgrade.sql" to skip over the sys.sp_MSIsContainedAGSession OR they may be able to provide you with that sp and you could create it.
Either way, I would reach out to support.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 2, 2021 at 4:36 am
Hello,
Did you ever figure out the cause and/or the solution? We've encountered the same issue after applying SQL 2019 CU14.
thank you
This was fixed for us by having an admin uninstall the CU, and then manually reinstall it (not using the automated deployment tool that installed the CU the first time); this time, no errors occurred, and the msdb110_upgrade.sql script was found in the database afterward.
Based on our experiences, it appears that the error was a fluke; unfortunately, the logs we have don't appear to indicate why the script failed to be included in the first run. Hopefully this solution works for you; good luck with it.
March 9, 2022 at 5:33 pm
Hello,
We faced the same issue with SQL Server 2019 CU 15.
We started our instance with the T902 flag to disable the upgrade script at startup as a workaround.
But I'm interested too in know how we can fix the cause of the issue, as I would like to be able to restart it without the flag and to finish this upgrade.
I also did the following attempt:
Find the faulty scripts on the server: <disk>\MSSQL15.<myinstance>\MSSQL\Install\msdb110_upgrade.sql
Launch it manually, and identify the same cause.
As we have no AvailabilityGroup, I replaced the numerous occurences of:
DECLARE @fIsContainedAGSession int
By:
DECLARE @fIsContainedAGSession INT=0
and commented:
EXECUTE @fIsContainedAGSession = sys.sp_MSIsContainedAGSession
as:
--EXECUTE @fIsContainedAGSession = sys.sp_MSIsContainedAGSession
From SSMS, this makes the script valid.
But if I try to restart my SQL Server service, without the flag, I still have the same issue.
I also tried to replace the <disk>\MSSQL15.<myinstance>\MSSQL\Install\msdb110_upgrade.sql with the modified version on my server. This did not help neither.
Any advice on the next step?
March 9, 2022 at 5:52 pm
My opinion - I would NOT have modified any upgrade related scripts or any scripts created and provided by Microsoft.
I would follow the same advice that I gave originally:
I would start by reaching out to Microsoft for support.
They may suggest that you rebuild master, but you will lose all of your logins and roles and other objects stored in master. Or they may have a script you can run to either modify "msdb110_upgrade.sql" to skip over the sys.sp_MSIsContainedAGSession OR they may be able to provide you with that sp and you could create it.
Either way, I would reach out to support.
Failing that, I would try the solution posted by the OP:
This was fixed for us by having an admin uninstall the CU, and then manually reinstall it (not using the automated deployment tool that installed the CU the first time); this time, no errors occurred, and the msdb110_upgrade.sql script was found in the database afterward.
Based on our experiences, it appears that the error was a fluke; unfortunately, the logs we have don't appear to indicate why the script failed to be included in the first run. Hopefully this solution works for you; good luck with it.
Mind you, since you modified the msdb110_upgrade.sql script and ran it on your system, you may also just be SOL and may need to do a fresh install and migrate the data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 9, 2022 at 6:53 pm
We've continued to see this issue randomly when patching SQL Server 2019 RTM so we opened a case with MS support, but they only suggested the following workaround. They were not interested in getting to the root cause.
June 15, 2022 at 6:34 pm
Update: We determined that this issue was caused by a third-party monitoring app which was monitoring the SQL Server service. It was attempting to resolve the perceived failure by starting the service during the installation of the CU. After disabling the monitor, we no longer see the issue occur.
October 17, 2023 at 11:43 am
I'm aware this is quite old discussion, just sharing in case someone will run into same error. For me looks like mdf and ldf for resources database was somehow not updated(files were still from 2019), I've coppied them from other server where patch installation went smoothly(resource db files created in 2023), restarted again sql service and update compleded sucesfully.
Solution shared from SQLOAT1 didn't work for me, still got same missing sp error, only difference was that script was executed by me.
January 11, 2024 at 5:05 am
Posting this in case it ends up helping someone else.
Client requested in-place upgrade of SQL 2012 to 2019. Upgrade ran flawlessly. Ran CU24 update, update had no problems until the very last step where it failed to start SQL services and the installer crashed.
Checked ERRORLOG and found the root cause of the issue was this missing stored procedure.
CU didn't show up in Update History so it couldn't be uninstalled, SQL started OK with -T902. Attempted Repair from SQL Server 2019 ISO. Repair succeeded/restarted server. Attempted CU24 install again, at which point the CU installed just fine.
Don't have a root cause exactly, but this worked reliably in my case.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply