Have you ever applied a patch to find that the server or service does not start after it was applied? There is nothing worse than working in a maintenance window trying to get services back up as quickly as possible when suddenly SQL will not start. Knowing that your best chance of identifying the problem is the SQL error log you quickly navigate to the logs to find something similar to the following.
If you are like me, you will scroll to the most recent entry and work backward. In this case, you would see “Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.” Even though your first reaction at this point may be to pray, there are other steps we should take. It is important to remember to take the error log in larger chunks. The final error may just be the last error in a series of errors and may not be the problem but a symptom of the problem.
Continuing up the log we see additional errors, Script level upgrade for database master failed because an upgrade script encountered an error. This error makes sense, we were applying a patch, although it doesn’t necessarily give us information on why the script failed. If we continue up the log we come to another error that is much more informative. Config statement cannot be used inside a user transaction, and even further up we see The procedure sys.sp_dbcmptlevel cannot be executed within a transaction. These errors tell us that SQL is running these scripts inside of a transaction. While this would have never been released if the procedure was called in an explicit transaction, so it must be an implicit transaction.
Trace Flag 902 will allow you to start SQL server without running the upgrade scripts giving you the opportunity to make the adjustments needed for the server to start. Microsoft describes TF902 as “Bypasses execution of database upgrade script when installing a Cumulative Update or Service Pack.” TF902 is not intended to be used for any length of time and it is not appropriate to run SQL with this trace flag except for this type of situation. Microsoft even specifically warns against running with this TF in
In this particular example, we are looking at 2008R2 instance with implicit transactions enabled as a default connection option. While this issue has been fixed in later service packs you would still need to disable the option to allow the update to run.
After starting the Service with TF902 and disabling implicit transactions, we can then remove TF902 and restart the service. We will then see that the service starts and the upgrade scripts complete successfully. We can now re-enable the implicit transactions option and our SQL server is patched.
There are many reasons why an update to SQL Server can fail. Trace Flag 902 was created to allow you an easy method to resolve the reason the update failed. Whether the reason was implicit transactions on an older version or limitations on the tempdb files sizes that caused the log file to not grow or any other reason. After identifying the root cause in the log file, you can use TF902 to start SQL to make whatever adjustment is necessary to facilitate the update. Just be sure to remove TF902 after you make those adjustments so the update can complete.