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')
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')
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.
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)
17 Comments. Leave new
This was invaluable! Not the same problem exactly but the troubleshooting steps were. Thanks
This saved my arse, thank you!
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.
Great! What was the issue you had?
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
Thanks! Had a similar problem and you helped me find it.
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?
dear, when you say you run the script manually, where did you found the script?
For example I’m looking for ISServer_upgrade.SQL
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.
We ran into exactly the same problem, two files with logical name “master”. This post was very helpful.
Thank you!
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
Thanks for this article. I couldn’t find much around, but luckily, I found this articule. I wasn’t so sure it was going to work for me because of all I was seeing (thought had nothing to do with me) but when it came to the very end, I checked on my database the parameters of the logical files which were not correct, i applied the correction and my problem was solved.
I was having problems with the backup process; it would start but at 80% it’d get stuck with error: read on (file) failed: 23 (failed to receive text from this error) Reason: 15100 and also the very same message with reason 15105
Thanks again.