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

SQL
16 Comments

I do apply patches to my SQL instances as and when they are released by Microsoft. This is important because I always feel keeping the bits up-to-date is essential because I don’t want to get infected as I travel quite a bit. Since I play a lot with my SQL Server, there are more chances that things are going to break sooner than your production server. Here is one such incident. Let us learn about how to fix script level upgrade.

I was trying to apply the patch to my SQL instance and it went fine. But then I was NOT able to start the SQL Server service. Here are the messages from SQL ERRORLOG.

2016-01-24 06:14:40.63 spid7s      Error: 537, Severity: 16, State: 3.
2016-01-24 06:14:40.63 spid7s      Invalid length parameter passed to the LEFT or SUBSTRING function.
2016-01-24 06:14:40.63 spid7s      Error: 912, Severity: 21, State: 2.
2016-01-24 06:14:40.63 spid7s      Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 537, state 3, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2016-01-24 06:14:40.64 spid7s      Error: 3417, Severity: 21, State: 3.
2016-01-24 06:14:40.64 spid7s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2016-01-24 06:14:40.64 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

I knew that I need to use trace flag 902 to bypass script upgrade mode and fix something. It was challenging to find what to fix. So I started looking for ‘sqlagent100_msdb_upgrade.sql’ which was found under “Install” folder in “C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL”. I started SQL using trace flag 902 as below

NET START MSSQL$SQL2014 /T902

Refer: SQL SERVER – 2005 – Start Stop Restart SQL Server from Command Prompt

Then I was able to connect to SQL Server because the problem script didn’t run due to trace flag. I ran the script manually and found below piece of code failing.

DECLARE @dataDirName NVARCHAR(520)
SELECT @dataDirName = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM MASTER.sys.master_files
WHERE (name = N'master')


SQL SERVER -  Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' script-upgrade-01

Now, we need to figure out what’s wrong and how to fix it. So, I modified query to get details.

SELECT DB_NAME(database_id) 'DB name', name 'logical name', physical_name
FROM MASTER.sys.master_files
WHERE (name = N'master')


SQL SERVER -  Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' script-upgrade-02

Now we can see a problem. The problem is because we have two rows and master. mdf doesn’t exist in the second row. It is failing because a test database had filename set correctly, but it’s logical name was set as master.

SQL SERVER -  Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' script-upgrade-03

To fix the problem I corrected the logical name of the user database which had been incorrectly set as master. Once this was done then, I stopped SQL and started it normally (without trace flag 902) and it was able to start successfully.

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

, , , ,
Previous Post
SQL SERVER – 2016 Error Reasons: Msg 10778, Level 16 Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported
Next Post
Interview Question of the Week #056 – How to fix Installation Failure – Rule “Setup account privileges” Failed in SQL Server

Related Posts

16 Comments. Leave new

  • This was invaluable! Not the same problem exactly but the troubleshooting steps were. Thanks

    Reply
  • Hi Paul,

    I have one issue while appliying the sp3 , 2012 on one of prod server, it was hanged at closing the wizard and got error “NT service SQL agent could not be started” and found in error log is

    “Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 226, state 6, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
    2016-11-24 21:54:35.54 spid4s Error: 3417, Severity: 21, State: 3.
    2016-11-24 21:54:35.54 spid4s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.”

    I have tried above whatever you have mentioned , but no luck…

    and also I have tried below , but there is no luck..my client need SQL services need to start without T902 trace flag.

    o Initially started the SP3 upgradation on server , he got an error “NT service SQL agent could not be started” , middle of up gradation.
    o We have tried uninstall of that SP3, it was removed from control panel successfully. But after reboot the instance shown SP3patch only.
    o Try to install that SP3 second time it went success , but hanged on closing the wizard and again error came “NT service SQL agent could not be started”.
    o We have enabled the trace flag T902 to skip the script upgradation , After enabled the trace flag SQL server started successfully without issue.
    o Stopped the agent and ran the below scripts on server for enabling the Agent XP’s.
    EXEC sp_configure ‘show advanced’, 1;
    RECONFIGURE;
    EXEC sp_configure ‘allow updates’, 0;
    RECONFIGURE;
    EXEC sp_configure ‘Agent XPs’, 1;
    RECONFIGURE;
    GO
    o Ran the below query for disabling the script upgradation.
    EXEC sys.sp_configure N’user options’, N’0′
    GO
    RECONFIGURE WITH OVERRIDE
    o As per below url , the issue (script upgradation related error) will come because of one of user database logical name set as a master logical file name. we ran same scripts on these instance but not found any user database having logical name same as a master.
    https://blog.sqlauthority.com/2016/01/30/sql-server-script-level-upgrade-for-database-master-failed-because-upgrade-step-sqlagent100_msdb_upgrade-sql/.
    o And removed trace flag from start of parameters , but no luck again SQl services went to offline and unable to start the services.

    Please suggest on this how can we proceed to fix this issue.

    .

    Reply
  • This saved my arse, thank you!

    Reply
  • Hans Jørgen Pedersen
    January 4, 2017 1:44 am

    Yep. This was really helpful!

    We had for some reason a database role which owned a schema – and a generic upgrade script which reported “completed with errors” because it wanted to drop that role (without proper checking).. That left my production database offline!

    Following the troubleshooting steps here focused my attention to the root cause, provided a way to fix it, and my production database is now back online without the need for restoring from backup.

    Reply
  • It is happening the same here.
    But I have a bigger problem, I can’t start the Query window due to a .NET Framework error… Do you have any suggestion for that?

    Thank you

    Reply
  • morgan canfield
    September 4, 2017 8:45 am

    Thanks! Had a similar problem and you helped me find it.

    Reply
  • I have the same problem after installing SP2 on SQL2014. But I do not see any user databases with logical name as MASTER. Please let me know if there could be any other reasons you know?

    Reply
  • dear, when you say you run the script manually, where did you found the script?
    For example I’m looking for ISServer_upgrade.SQL

    Reply
  • SARBU GABRIELA
    June 21, 2018 1:32 pm

    I had the same issue when installing SP4 for SQL 2008 (still have couple of months before i upgrade this machine, so i decided to still apply this).

    The error from my log was:

    “2018-06-20 22:20:16.88 spid6s Error: 5133, Severity: 16, State: 1.

    2018-06-20 22:20:16.88 spid6s Directory lookup for the file “D:\DW_Database\temp_MS_AgentSigningCertificate_database.mdf” failed with the operating system error 2(failed to retrieve text for this error. Reason: 15100).

    2018-06-20 22:20:16.88 spid6s Error: 1802, Severity: 16, State: 1.

    2018-06-20 22:20:16.88 spid6s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    2018-06-20 22:20:16.88 spid6s Error: 912, Severity: 21, State: 2.

    2018-06-20 22:20:16.88 spid6s Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

    2018-06-20 22:20:16.89 spid6s Error: 3417, Severity: 21, State: 3.

    2018-06-20 22:20:16.89 spid6s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

    2018-06-20 22:20:16.89 spid6s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.”

    The error refers to unable to create a db, so i started the sql service by running “NET START MSSQLSERVER /T902″ and tried to create a dummy db. I had the same error.

    I checked the script that was running (C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Upgrade\sqlagent100_msdb_upgrade.sql) and found the line with the error:

    ” –create a temporary database in order to get the path to the ‘Data’ folder

    IF (EXISTS (SELECT name

    FROM master.dbo.sysdatabases

    WHERE (name = N’temp_MS_AgentSigningCertificate_database’)))

    BEGIN

    DROP DATABASE temp_MS_AgentSigningCertificate_database

    END

    CREATE DATABASE temp_MS_AgentSigningCertificate_database”

    The solution was to check the default data directory for my server – it was a folder on D drive (D:\DW_Database), and that folder didn’t existed anymore, was renamed locally in “Database”. After the correction, i closed SSMS, restarted sql service and waited to see if the service stops again, for almost 5 intense minutes :). It was fine, as the upgrade script resumed and finished succesfully.

    The service was started normally, and no issues seen after.

    Reply
  • We ran into exactly the same problem, two files with logical name “master”. This post was very helpful.
    Thank you!

    Reply
  • You are awesome!! I followed the troubleshooting steps and fixed my issue. My publisher and distribution databases were created from a 2008r2 database. The patch stored procedure was looking for a system table MSreplservers in the distribution database which did not get created. My SQL Server was upgraded to 2017 but my replication database compatibility is still set to 2008r2 (not sure if that’s why the table never got created in the first place). I manually created this table from another distribution database and everything worked after that.

    Thanks and keep the good work.

    George

    Reply

Leave a Reply

Menu