This blog post is way overdue (check the dates in the errror log below), but I promised our sysadmin that I would write it, so here it is. Hopefully, it’ll help some of you with this aggravating issue. During an upgrade of our SQL cluster, we ran into the following error as we attempted to upgrade one of the instances:
2014-04-15 23:50:14.45 spid14s Error: 574, Severity: 16, State: 0.
2014-04-15 23:50:14.45 spid14s CONFIG statement cannot be used inside a user transaction.
2014-04-15 23:50:14.45 spid14s Error: 912, Severity: 21, State: 2.
2014-04-15 23:50:14.45 spid14s 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.
2014-04-15 23:50:14.45 spid14s Error: 3417, Severity: 21, State: 3.
2014-04-15 23:50:14.45 spid14s 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.
Google wasn’t helpful; there’s apparently lots of potential fixes for this, none of which helped. The closest match we found was that we had some orphaned users in a few databases (not system databases), which we corrected; the upgrade still failed. We eventually had to contact Microsoft support, and work our way up the second level technician. Before I reveal the fix, let me give a little more background on how we got orphaned users.
You see, shortly after we upgraded to SQL 2012 (about a year ago), we did what many companies do; we phased out a service offering. That service offering that we phased out required several database components, including a SQL login associated with users in the database, and several maintenance jobs that were run by SQL Agent. When we phased out the service, those jobs were disabled, but not deleted. Our security policy tracks the last time a login was used; if a login isn’t used within 60 days, it’s disabled. 30 days after that (if no one notices), the login is deleted. Unfortunately, our implementation of this process missed two key steps:
- The associated user in each database was not dropped with the login (leaving an orphan), and
- any job that was owned by that login was also not dropped or transferred to a sysadmin.
The latter was the key to our particular situation; the upgrade detected an orphaned job even though that job was disabled, and blocked the upgrade from going forward. Using trace flag –T902, we were able to start the server instance and delete the disabled job. We then restarted the server without the trace flag, and the upgrade finished successfully.
Resources:
Find and fix all orphaned users for all databases.
Brent Ozar Unlimited’s sp_blitz will find jobs that are owned by users other than sa.
Great post!!!
We need more of these to help others
thomas