Skip to the bottom of the post for the TL;DR section for the on-call DBA where you can see an immediate cause and solution.

SQL Server won’t start and at first, I couldn’t figure out why. When I logged off for the day on Friday, everything was happy, but when I logged on Monday morning, a heavily used test instance of SQL was not running and wouldn’t start.

Quick side note, and it’s funny looking back on now, but in the moment it sucks. You ever have to start the SQL Server Service from Configuration Manager, and when you right click -> Start, or right click -> Restart you see that green progress bar start to go and under normal healthy circumstances, it probably gets a third of the way through before SQL kicks over. But when you start to see that progress bar start to make more and more progress, you know before it even fails on you that something isn’t going to work right. You start to get that sinking feeling in your stomach. That was me.

Anyway, after trying to jump start the SQL Server a couple times, I took to the Event Viewer to see if that could give me more info than “The service didn’t start in a timely manner” or whatever the exact phrasing is. It was there that I was able to find more clues.

Looking In Event Viewer

When I opened up Event Viewer, I immediately went to the Application Event Log. In there I saw the following:

Event ID: 912

Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 574, state 0, 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.

Event ID: 3417

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.

These are two menacing messages to see. However, it is a little misleading. When I first read 3417, I immediately thought corruption and thought I may have to rebuild a server or try to restore a system database. It wasn’t until I searched 912 that I was able to get a bit more insight into a less invasive recovery option.

Stopping The Bleeding

At this point, my goal was to do whatever I could to get the instance back up and running so the databases are available for development as this is a heavily used test server. When I Googled the 912 message, I came across an explanation as well as a valuable trace flag. Here’s what I found:

This can pop up when a CU or a GDR gets applied to a SQL Server, and after a restart when the internal upgrade scripts get ran against the system databases, if there are problems that occur resulting in the script(s) not running successfully, it can leave you in a position where the 574 error is thrown which indicates a problem when the CONFIG clauses are being ran.

The temporary fix for this was to add TF 902 to the startup parameters for the SQL Server Service. This is what that trace flag does:

It basically tells SQL to skip running the upgrade scripts to the system databases after a patch. This allows you to bring the server online and troubleshoot but will leave you in a “partially patched” state until the upgrade scripts are ran. This is why it is so important to treat 902 as a band-aid and not a long term solution.

Here’s how I added TF 902 to the list of startup parameters for SQL:

1. Open up Configuration Manager
2. Click on SQL Server Services
3. Right click on the SQL Server Engine Service for the instance you’re troubleshooting -> Properties
4. Click on the Startup Parameters tab
5. In the text box where it says “Specify a startup parameter” type -T902 and hit Add
6. Then click OK, it will prompt you that SQL needs to be restarted to take effect
7. Right click SQL Server and hit Start

This should bring up SQL so you can do some more digging and troubleshooting to find the root cause.

Please Note: Trace flag 902 is a temporary solution and is not meant to be a long term fix. All this TF does is stop SQL from running any internal upgrade scripts post CU or GDR application.

Chasing Down A Root Cause

This part was tough at first, because all of the suggested information I could find pointed to causes that didn’t apply to the impacted server. Don’t you love that?

There seems to be some documented reports out there of this exact issue, but the recommended steps I initially tried to validate to resolve this weren’t applicable. Here is what I did nonetheless because they are still potential causes of this issue:

Checking to see if allow updates was set to 1

• I haven’t heard of this one before troubleshooting this issue, but apparently it’s a dead option since SQL 2005. Microsoft stopped letting you mess with system tables directly (probably a smart idea) but you can still see it in sp_configure. If this was set to 1, it just leaves a pending value the engine can never apply, which means every plain RECONFIGURE on the instance fails from that moment on. The upgrade scripts call RECONFIGURE internally, so if this option is set, patch day is the day it finally starts screaming at you.

SELECT name, value, value_in_use
FROM sys.configurations
WHERE name = 'allow updates';

Seeing if user options with IMPLICIT_TRANSACTIONS is set

• From my research, this could have a potential to be the culprit, even though I was pretty confident we didn’t set this one to a value that would make an impact. This is server wide and is a setting that defines default SET options for every new connection, including the internal sessions that the script upgrade phase opens at startup. Bit value 2 is IMPLICIT_TRANSACTIONS. If that bit is on, the upgrade session’s first DML quietly opens a transaction, and by the time the script reaches RECONFIGURE it’s sitting inside a transaction it never asked for. Boom, 574. But, no luck on this being the issue.

SELECT CASE WHEN (CAST(value_in_use AS int) & 2) = 2
THEN 'IMPLICIT_TRANSACTIONS is ON'
ELSE 'not set' END AS implicit_tx
FROM sys.configurations
WHERE name = 'user options';

Looking at login triggers

• Server-scoped logon triggers fire for the internal script upgrade sessions too, and a trigger that opens or wrecks transaction state is a documented way to kill a script upgrade. If you have one, disable it before you try again.

SELECT name, is_disabled
FROM sys.server_triggers;

Checking on potential legacy 2005 SSIS data

• This one was a non starter in my mind because we never even had SSIS on this server, but the SQL Log pointed to this explicitly. Right before things start to error out in the log, it says “Moving 2005 SSIS Data to 2008 tables” and then goes through the steps of moving folders, packages, logs, and dropping yukon tables. Again I thought this was odd that it explicitly calls this out right before things start to go south when we never had SSIS on this server installed.
  It just so happens that these are just PRINT statements and don’t actually show up from actual work being done due to a conditional satisfaction. The upgrade script announces every section header whether or not there’s anything to actually migrate. This is what I used to confirm there’s no legacy SSIS related data:

SELECT name
FROM msdb.sys.tables
WHERE name LIKE 'sysdts%';

When these checks showed up negative for me, I was left scratching my head until I re-scanned the SQL Log and found something that was staring me in the face the whole time.

The Needle In The Haystack

Looking at the SQL Logs again, I saw the following message:

A problem was encountered granting access to MSDB database for login ‘(null)’. Make sure this login is provisioned with SQLServer and rerun sqlagent_msdb_upgrade.sql

To be completely honest, this just looked like noise when I first was trying to troubleshoot this and completely skipped over it. But turns out, this was the root cause of the problems all along.

Another entry in the logs made this even more apparent that this was the problem:

Granting login access ‘DOMAIN\UserName’ to msdb database…

Adding user ‘DOMAIN\UserName’ to SQLAgentUserRole msdb role…

I redacted the domain and username but there was a domain user that was explicitly being added to the msdb user mapping which was odd.

Painting The Final Picture

So, I’m going to walk through the entire cause step by step here (and no, AI did not write this, I just like numbered lists and bullet points :)):

1. Whenever a CU or a GDR (security update) gets applied to a SQL Server, upon next restart or startup, there are internal upgrade scripts that are ran against system databases. One of them is called sqlagent_msdb_upgrade.sql. The Agent grant logic that throws the ‘(null)’ message runs as part of the msdb110_upgrade step, which is why the failure is connected to msdb110_upgrade.sql even though the message tells you to rerun sqlagent_msdb_upgrade.sql.
   • I did not think that a patch got applied to this SQL Server, but patch Tuesday happened and apparently infrastructure applied security updates. I was able to see this and prove it by looking in the SQL Server Update Cache.
2. When this upgrade script runs, the SQL Agent portion of the script enumerates non-sysadmin job owners and grants each one msdb access.
3. There was a SQL Agent job on this SQL Server owned by a user that only has server access through an AD group. No individual login means the owner’s SID does not resolve which means the script will throw the ‘(null)’ finding in the SQL Log.
4. The grant for the account to have access to msdb fails inside the script’s TRY / CATCH block and an error in the TRY block leaves the transaction invalidated so it is left in an open and uncommittable state.
5. The script keeps executing inside that broken transaction until it hits the sp_configure / RECONFIGURE portion.
6. The RECONFIGURE can’t run inside a user transaction which results in Error 574 being thrown.
7. The script upgrade aborts, master db is left offline, and the instance can’t start.

Just to clarify, there is nothing wrong with group-based access. In fact, that is preferred as opposed to individual login access for administrative management. The problem here is a user login owning a job and the internal upgrade script not being able to resolve the SID since that user only lives in a group on the server.

Everything was fine for over a month and only happened to cause problems now because there was a patch applied and then the server was rebooted. Before that, everything was basically sitting in a staged state to fail.

The Fix

The solution here was to change that job’s owner from the domain user account to sa, remove TF 902 as a startup parameter in SQL and reboot the SQL Server service. From there, everything came up fine and SQL could start. Here’s a query you can use to find SQL Agent jobs in your environment that are owned by individual users and not sa where the owners also do not have a matching SID at the login level meaning they’re either in a group or orphaned, but if they’re orphaned, then the job most likely isn’t working anyways:

SELECT j.name, SUSER_SNAME(j.owner_sid) AS resolved_owner
FROM msdb.dbo.sysjobs j
WHERE SUSER_SNAME(j.owner_sid) IS NULL
OR j.owner_sid NOT IN (SELECT sid FROM sys.server_principals)

Here’s what you can use to change job ownership to sa from an explicit user:

EXEC msdb.dbo.sp_update_job @job_name = N'...', 
@owner_login_name = N'sa';

Make sure to take a peek at replication related jobs and make sure to look at what the job does before re-assigning to sa.

TL;DR For The On-Call DBA

1. SQL won’t start after patching and you see this in the Application Event Logs:
   • Event ID: 912
     Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 574, state 0, 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.
   • Event ID: 3417
     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.
2. Add Trace Flag 902 to the list of startup parameters in SQL.
   • This will prevent SQL from running internal system database upgrade scripts which is what is preventing SQL from starting.
   • This is a temporary band-aid to get SQL to run, do not leave this running long term.
3. Look in the SQL Logs and filter for error 574, take note of the time it is logged, remove the 574 filter and look around there to see what messages are logged.
4. If you see keywords like the following, then it is most likely due to a user login owning a SQL Agent Job that does not have a matching SID:
   • A problem was encountered granting access to MSDB database for login ‘(null)’. Make sure this login is provisioned with SQLServer and rerun sqlagent_msdb_upgrade.sql
5. Find the jobs owned by user logins that don’t have a corresponding SID at the server level:

SELECT j.name, SUSER_SNAME(j.owner_sid) AS resolved_owner
FROM msdb.dbo.sysjobs j
WHERE SUSER_SNAME(j.owner_sid) IS NULL
OR j.owner_sid NOT IN (SELECT sid FROM sys.server_principals)

6. Change job ownership from the user to sa:

EXEC msdb.dbo.sp_update_job @job_name = N'...', 
@owner_login_name = N'sa';

7. Remove TF 902 from the list of startup parameters in Configuration Manager, and start up SQL.

References

Microsoft Learn: SQL Server upgrade fails with error code 574 when executing update database scripts https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/install/windows/sql-server-upgrade-failed-error-574

Microsoft SQL Server Support Blog (Tech Community): Patch upgrade failed. ‘Error: 574 CONFIG statement cannot be used inside a user transaction.’ https://techcommunity.microsoft.com/t5/sql-server-support-blog/patch-upgrade-failed-error-574-config-statement-cannot-be-used/ba-p/1937250

Microsoft Learn: TRY…CATCH (Transact-SQL), uncommittable transactions / XACT_STATE https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql

Microsoft Learn: Start SQL Server with trace flag 902 https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/install/windows/sql-server-upgrade-failed-error-574#start-sql-server-with-trace-flag-902

Microsoft Learn: user options server configuration option https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option

Microsoft Learn: sp_update_job
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-job-transact-sql