There is a failure pattern for SQL Server service pack and cumulative updates that I had somehow not encountered up until now. The SP or CU runs various upgrade scripts while it is doing its thing and, rarely, one of these will fail due to something that is different about that particular server.
The details vary quite a bit from one situation to another, but the outcome is the same: an upgrade script failure, a master database recovery failure (at least in all the cases I found), and a SQL Server service that won’t start. You can allow it to start by adding trace flag 902, which prevents the upgrade script from running again, but that doesn’t actually fix the problem. To fix it you have to figure out exactly what went wrong, and fix that. The fix can be totally different each time. And as far as I can see, nobody had ever run into this particular scenario before and happened to post about it publicly.
This came up for me when someone asked me to look at a SQL Server 2012 SP4 installation failure. Having not seen this before, it was rather confusing to behold. The log was telling us to restore the master database because it had failed recovery, yet obviously it had been up and running because a long script had been running against another system database, msdb in this case. So I sensed a potential bogus message.
Digging more deeply, it became obvious that the real problem was in msdb, or so it seemed. There is a table there called dbo.sysssispackagefolders, which contains the representations of folders used to store SSIS packages in MSDB. You can display the folders by connecting to Integration Services using SSMS. (I think this feature may be gone in newer versions in favor of using SSISDB and project deployment.) The upgrade script was attempting to create the Generated folder under the Data Collectors folder. Unfortunately, however, the Generated folder already existed and did not need to be created, with the result that the upgrade failed and the server died. It seems a little harsh, for something that doesn’t even matter, but that’s the way it works.
The really interesting aspect of this failure is that there was nothing wrong with either the upgrade script or dbo.sysssispackagefolders. The script was properly coded to test whether ‘Generated’ existed or not, and there was nothing whatsoever wrong with the way ‘Generated’ was represented in the table. The script had run successfully on other servers at this site. It had just successfully performed the same test on the Data Collectors folder. It just plain didn’t work, for no good reason.
It made me think of my first job in college, as a student programming assistant helping other students with their assignments. Often they would come to me with the story that they knew their program, which wasn’t working, was perfect and that there must be something wrong with the compiler or the computer itself. My job was to convince them otherwise and help them to see what to do instead of blaming the system software or hardware. And now, 48 years later, it was happening to me.
The solution to the problem was to use SSMS to empty and delete the Generated folder, allowing the upgrade script to re-create it without error. This makes no real sense to me, but the server is back up and running and that is my job now, not convincing people that the system software works correctly. So case closed. But what really happened? I have no idea.