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

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 ( http://blog.sqlauthority.com )

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLRY
分页:12
转载请注明
本文标题:SQL SERVER Unable to Start Services After Patching (sqlagent_msdb_upgrade.sql ...
本站链接:http://www.codesec.net/view/480946.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(29)