Unable to start services after patching - msdb110_upgrade.sql' encountered error 574, state 0, severity 16.

09 September,2018 by Jack Vamvas

Question : During a recent round of patching , I've been frequently seeing msdb110_upgrade.sql' encountered error 574, state 0, severity 16 in the Event Viewer when the service for the SQL Server Instance is attempting to start.

How can I resolve this issue? The service will not currently start.

I've checked in the SQL Server Error log file via a text editor and can see 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 

The lines in the SQL Error Log are:

2018-09-09 13:41:56.88 spid5s      Granting login access'xxxx\ID' to msdb database...
2018-09-09 13:41:56.89 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
2018-09-09 13:41:56.89 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
2018-09-09 13:41:56.89 spid5s      

Answer: I've experienced similar issues and on each occasion it's been a different underlying reason . The way I've approached the problem is :

Step 1: Extract the error lines from the SQL Server Error Log file

Step 2 : Start the SQL Service with the trace flag 902   -  so the start command will be :     NET START MSSQL$MYSERVICE /T902

This will give you enough time to fix the root cause ,

Step 3 : When the fix is is applied then restart the sql service but without the trace flag

 

 Read More on trace flags

 Trace flag 610 and High performance data loading (SQL Server DBA)

Slow SQL Server Backup and Restore with DBCC TRACEON (3004 ...

List SQL Server DBCC commands using trace 2520 – documented and undocumented commands

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Unable to start services after patching - msdb110_upgrade.sql' encountered error 574, state 0, severity 16.


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer