In the world of SQL Server, applying patches and upgrades is a routine task. However, sometimes these updates can cause unexpected issues that can disrupt the normal functioning of the SQL Server. One such issue that I have come across frequently is the inability to start services after patching.

When clients encounter this issue, they often reach out for help, and it is important to understand the root cause of the problem in order to provide an effective solution. In most cases, the issue lies with the upgrade script failure. The error message may vary, but the underlying cause remains the same.

One common error message that I recently encountered was:

2016-08-16 20:41:57.96 spid9s 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

Upon further investigation, I discovered that the error was caused by a login that owned a schema and was preventing the SQL Server from starting. This presented a challenge as I was unable to drop the schema due to the SQL Server not being accessible.

Fortunately, there is a trace flag, 902, that can be used to bypass the upgrade script and start the SQL Server. By enabling this trace flag, you can start the SQL Server and identify the cause of the issue from the ERRORLOG file. Once the cause is identified, you can take the necessary steps to fix it.

It is important to note that trace flags should be used with caution and only when necessary. They are powerful tools that can help in troubleshooting, but they should be removed once the issue is resolved and the SQL Server is started normally.

Have you ever encountered any issues with upgrade scripts after patching SQL Server? If so, what troubleshooting steps did you take? Let me know in the comments below!