SQL SERVER – Unable to Start Services After Patching (sqlagent_msdb_upgrade.sql)

In the recent past, I have heard this issue, at least once or twice every month. Whenever I try to help a such client and I think I know the issue, I get something new. They said that they have applied a patch and after that they were not able to access the SQL server and it is going offline. Here is a blog post where I discuss about Unable to Start Services After Patching (sqlagent_msdb_upgrade.sql).

I immediately told them that this would be an issue with upgrade script failure.

SQL SERVER – Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’

They confirmed that the issue is with upgrade script, but the error message is not the same. I asked them to send the ERRORLOG file to me and I found below error

2016-08-16 20:41:57.95 spid9s Granting login access ‘pan\svc-sql-agt’ to msdb database…
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

I have never seen above error earlier, but I went and searched for “sqlagent_msdb_upgrade.sql” file and found it in INSTALL folder. Here are the lines causing error in that file.

--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)
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

Solution/Workaround

I did some more troubleshooting and found that ‘pan\svc-sql-agt’ was owning a schema and hence we were not able to drop it.

The biggest challenge was that SQL was not getting started and I was not able to connect. Fortunately, there is a trace flag 902 which can help in starting SQL by bypassing the script. ERRORLOG can tell the cause and trace flag helps in fixing the cause.  So, whenever you encounter any issue with upgrade script and need to troubleshoot, then use trace flag 902.  You need to make sure to remove it and start SQL normally.

Have you ever used any such trace flags?

Reference: Pinal Dave (https://blog.sqlauthority.com)