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 - Unable to Start Services After Patching (sqlagent_msdb_upgrade.sql) sscm-stop-01-800x162

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)

, ,
Previous Post
SQL SERVER – Fix: The Cluster Resource Could not be Deleted Since it is a Core Resource
Next Post
SQL SERVER – Error: Property BackupDirectory is Not Available for Settings

Related Posts

2 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.

    Reply
  • i used -T902 sqlserver come up but not able to stop upgrade script ??

    Reply

Leave a Reply

Menu