Patch upgrade failed. ‘Error:574 CONFIG statement cannot be used inside a user transaction.’
This article is contributed. See the original author and article here.
Customer failed to apply patch. SQL Service cannot start. They received below errors in SQL Error logs.
2020-11-24 20:09:21.59 spid5s Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.
2020-11-24 20:09:21.59 spid5s Error: 574, Severity: 16, State: 0.
2020-11-24 20:09:21.59 spid5s CONFIG statement cannot be used inside a user transaction.
2020-11-24 20:09:21.59 spid5s Error: 912, Severity: 21, State: 2.
2020-11-24 20:09:21.59 spid5s 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.
2020-11-24 20:09:21.60 spid5s Error: 3417, Severity: 21, State: 3.
2020-11-24 20:09:21.60 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.
2020-11-24 20:09:21.60 spid5s SQL Server shutdown has been initiated
Just before ‘Error: 574’, I noticed ‘show advanced options’ in SQL Error log. I suppose it failed in ‘sp_configure’ scripts. I made below test to reproduce this error.
Open a new query window. Run begin tran first, then run sp_configure query. We can reproduce the same error.
begin tran
sp_configure ‘show advanced options’,1
go
reconfigure
go
According to the test, we felt sure that there was an uncommitted transaction when running ‘msdb110_upgrade.sql’. But where has it come from ? I read SQL Error logs again and found below information:
2020-11-24 20:09:19.52 spid5s Granting login access’DomainUserA’ to msdb database…
2020-11-24 20:09:19.55 spid5s 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
2020-11-24 20:09:19.80 spid5s Granting login access’DomainUserB’ to msdb database…
2020-11-24 20:09:19.80 spid5s 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
Even though, there are no error code, but those information indicates ‘msdb110_upgrade.sql’ encountered an issue before Error: 574. According to ‘msdb110_upgrade.sql’ scripts, I found below part
——————————————————————————–
–wals thru all non sysadmin job owners
DECLARE job_nonsysadmin_owners_cursor CURSOR LOCAL FOR
SELECT DISTINCT j.owner_sid FROM sysjobs j
FOR READ ONLY
OPEN job_nonsysadmin_owners_cursor
FETCH NEXT FROM job_nonsysadmin_owners_cursor INTO @owner_sid
WHILE (@@fetch_status = 0)
BEGIN
SELECT @owner_name = SUSER_SNAME(@owner_sid)
IF @owner_name IS NOT NULL
BEGIN
–is job owner member of sysadmin role?
BEGIN TRY
EXECUTE AS LOGIN=@owner_name — impersonate
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(‘sysadmin’),0) — check role membership
REVERT — revert back
END TRY
BEGIN CATCH
SET @is_sysadmin = 0
END CATCH
IF @is_sysadmin = 0
BEGIN
–add job_owner to the SQLAgentUserRole msdb role in order to permit the job owner to handle his jobs
–has this login a user in msdb?
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE (sid = @owner_sid) OR (LOWER(name collate SQL_Latin1_General_CP1_CS_AS) = LOWER(@owner_name collate SQL_Latin1_General_CP1_CS_AS)))
BEGIN
PRINT ”
PRINT ‘Granting login access”’ + @owner_name + ”’ to msdb database…’
BEGIN TRY
EXEC sp_grantdbaccess @loginame = @owner_name
END TRY
BEGIN CATCH
RAISERROR(‘A problem was encountered granting access to MSDB database for login ”%s”. Make sure this login is provisioned with SQLServer and rerun sqlagent_msdb_upgrade.sql ‘, 10, 127) WITH LOG
END CATCH
END
PRINT ”
PRINT ‘Adding user ”’ + @owner_name + ”’ to SQLAgentUserRole msdb role…’
BEGIN TRY
EXEC sp_addrolemember @rolename = ‘SQLAgentUserRole’, @membername = @owner_name
END TRY
BEGIN CATCH
RAISERROR(‘A problem was encountered adding user ”%s” to SQLAgentUserRole. Make sure this is a valid user in MSDB database and rerun sqlagent_msdb_upgrade.sql ‘, 10, 127) WITH LOG
END CATCH
END
END
FETCH NEXT FROM job_nonsysadmin_owners_cursor INTO @owner_sid
END
DEALLOCATE job_nonsysadmin_owners_cursor
It seems this scripts will find out non sysadmin Agent job owners. Grant msdb access to those job owner accounts. However, ‘DomainUserA’ and ‘DomainUserB’ were not logins of this SQL instance. But they are still job owners. This is the reason above ‘try…catch’ block throw out below messages:
2020-11-24 20:09:19.55 spid5s 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
I consulted below official document. ‘try…catch’ block may cause an uncommitted transaction.
Uncommittable Transactions and XACT_STATE
If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block.
Now we found root cause. Customer removed ‘DomainUserA’ and ‘DomainUserB’ from job owners. Patch upgrade was successful.
Recent Comments