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.
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)
4 Comments. Leave new
Hi Pinal,
Thanks for nice article. Yes I had one such encounter with Trace flag 3608. I received message that [model] database is in RESTORING mode. I restarted the services hoping to resolve the issue (Yes I was junior DBA that time :)). SQL Server did not come up. Logs suggested the same issue except for the fact this time whole server was down. So I had started the services with T3608, and restored the model database with command like ‘RESTORE DATABASE [model] WITH RECOVERY’ or something like it.
i used -T902 sqlserver come up but not able to stop upgrade script ??
Thanks for this article. Saved my QA Server today after an update. Starting up with -T902, deleting the schema and restarting without 902 did the trick.
Dave, this led me to find the issue on my side , in my case was a needle in a haystack.
Adding user ‘public’ to SQLAgentUserRole msdb role…
A problem was encountered adding user ‘(null)’ to SQLAgentUserRole. Make sure this is a valid user in MSDB database and rerun sqlagent_msdb_upgrade.sql
A problem was encountered adding user ‘(null)’ to SQLAgentUserRole. Make sure this is a valid user in MSDB database and rerun sqlagent_msdb_upgrade.sql
i found that message , and saw the “public” word, and that didn’t sound right, i kept looking, i even tried to run sp_addrolemember ‘sqlagentuserrole’,’public’ which failed.
after a while , i found a SQL AGENT JOB with the owner as …. public
changed to other existing login, and the patch went through
hope this works for someone