SQL Server offline after applying service pack
In this post I´ll review two cases that you may bump into when applying service packs in SQL Server.
Case 1
You need to install a SQL Server service pack, so you plan, test and got everything ready to apply it in production. You run the installation executable and it finishes successfully. Almost there... when SQL Server service starts it suddenly became offline... What happened? With fingers crossed you try again, same thing.
The next logical step is to check logs, so you find something similar to:
2017-03-21 22:29:04.18 spid6s Database 'master' is upgrading script 'sqlagent100_msdb_upgrade.sql' from level 0 to level 2.
2017-03-21 22:29:04.18 spid6s ----------------------------------------
2017-03-21 22:29:04.18 spid6s Starting execution of PREINSTMSDB100.SQL
2017-03-21 22:29:04.18 spid6s ----------------------------------------
2017-03-21 22:29:04.23 spid6s Error: 15002, Severity: 16, State: 1.
2017-03-21 22:29:04.23 spid6s The procedure 'sys.sp_dbcmptlevel' cannot be executed within a transaction.
2017-03-21 22:29:04.25 spid6s Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2017-03-21 22:29:04.25 spid6s Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2017-03-21 22:29:04.25 spid6s Error: 574, Severity: 16, State: 0.
2017-03-21 22:29:04.25 spid6s CONFIG statement cannot be used inside a user transaction.
2017-03-21 22:29:04.25 spid6s Error: 912, Severity: 21, State: 2.
2017-03-21 22:29:04.25 spid6s Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_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.
2017-03-21 22:29:04.25 spid6s Error: 3417, Severity: 21, State: 3.
2017-03-21 22:29:04.25 spid6s 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.
2017-03-21 22:29:04.25 spid6s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
What transaction? I don´t know the details about scripts executed by the service pack!
Even if you enable other trace flags trying to check what script is doing, it will give little clue in this case.
Then you recall of a nice instance configuration called "implicit transactions". Check/change this configuration seems a good action plan, but how to do it if the instance is offline?
One approach is to use trace flag 902 to bypass execution of database upgrade script when installing a Cumulative Update or Service Pack, so you can have a chance to check configuration and makes changes to instance.
Please read careful the documentation with info about TF 902: https://msdn.microsoft.com/en-US/library/ms188396.aspx
"Bypasses execution of database upgrade script when installing a Cumulative Update or Service Pack. If you encounter an error during script upgrade mode, it is recommended to contact Microsoft SQL Customer Service and Support (CSS) for further guidance. For more information, see this Microsoft Support article.
WARNING: This trace flag is meant for troubleshooting of failed updates during script upgrade mode, and it is not supported to run it continuously in a production environment. Database upgrade scripts needs to execute successfully for a complete install of Cumulative Updates and Service Packs. Not doing so can cause unexpected issues with your SQL Server instance."
OK? So let's continue...
Checking the instance properties you can see that default connection options has implicit transactions set. You disable the option and now need to verify if this was the culprit of the problem. Then you need to REMOVE trace flag 902, so upgrade scripts can run, restart SQL Server instance and check the outcome of it.
Congratulations! Now all the scripts were successfully executed and your instance has the patch applied, so you can reconfigure "implicit transactions" if this is what you desire for you instance.
Case 2
Following a similar line of troubleshooting, suppose you are applying another patch, the setup wizard goes successfully and when the instance starts, it goes offline. Checking ERRORLOG now you see:
2017-03-22 17:48:51.06 spid5s Error: 9002, Severity: 17, State: 4.
2017-03-22 17:48:51.06 spid5s The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
2017-03-22 17:48:52.08 spid5s Error: 912, Severity: 21, State: 2.
2017-03-22 17:48:52.08 spid5s Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 9002, state 4, severity 17. 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.
2017-03-22 17:48:52.08 spid5s Error: 3417, Severity: 21, State: 3.
2017-03-22 17:48:52.08 spid5s 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.
2017-03-22 17:48:52.08 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Alright, now TempDB transaction log is full, so you double check the files and see that it has only 512 KB, the same size of modellog.mdf (what a coincidence!). But why didn´t the transaction log didn´t grow? Maybe someone wanted to guaranteed that all the data files keep with the same size and by mistake disabled transaction log auto growth, so when SQL Server resized the file following model database, it does not have room to even process the upgrade scripts.
Trace flag 902 again! Add it, start SQL Server instance, reconfigure TempDB transaction log to growth, stop instance, remove TF 902 and restart SQL Server instance again and... great! Everything is up and running.
Conclusion
Above I provided some examples involving issues with upgrade scripts and key messages are:
- ALWAYS check what is going on using SQL Server ERRORLOG
a. Without any error information, how will you troubleshoot the issue - When dealing with upgrade scripts issues, trace flag 902 can be your friend.
a. Remember the docs and know what are you doing: https://msdn.microsoft.com/en-US/library/ms188396.aspx - Test you upgrade process in other environments, production SQL Server is not a great place to be the first time you apply a patch or will be using a different trace flag.
More than the issues per se, I hope this will help you to think clearly when facing an issue and consider your troubleshooting steps, that may be the difference between solving it or not.
Luciano Caixeta Moreira - [ Luti ]
luticm79@hotmail.com https://www.linkedin.com/in/luticm www.twitter.com/luticm