Hello,

I've encountered issue during installation of SP1 to SQL Server 2012. After upgrade I'm getting this error in Event Log:

Script level upgrade for database 'master' failed because upgrade step 'msdb110_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.

Can someone point me direction how I can fix it? This is a production server and currently only way to make it working is to use T902 flag in SQL Server startup params. I've found some suggestions to check Data path if it exists but it does so this is not the issue here. Any ideas?

I've found also here

http://www.sqlservercentral.com/Forums/Topic1377073-1550-1.aspx#bm1378279

suggestions for similar issue with SQL Server 2008 which that I should do:

Via ssms:
From msdb:
Delete:
dc_admin role
Dc_operator role
Dc_proxy role
UlitityCMRReader role
UtilityIMRReader role
UtilityIMRWriter role

but for not I didn't tried it yet. This is standalone SQL Server instance.

Any help really appreciated.

Regards

Share the messages from SQL errorlog which are above this error which you shared related to script upgrade failure

Hello,

I hope this is what you are asking:

2012-12-15 01:51:17.60 spid8s      Error: 537, Severity: 16, State: 3.
2012-12-15 01:51:17.60 spid8s      Invalid length parameter passed to the LEFT or SUBSTRING function.
2012-12-15 01:51:17.60 spid8s      Error: 912, Severity: 21, State: 2.
2012-12-15 01:51:17.60 spid8s      Script level upgrade for database 'master' failed because upgrade step 'msdb110_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.
2012-12-15 01:51:17.62 spid8s      Error: 3417, Severity: 21, State: 3.
2012-12-15 01:51:17.62 spid8s      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.
2012-12-15 01:51:17.62 spid8s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Start SQL with trace flag 902 and run the below code and see if that gives the same error " Invalid length parameter passed to the LEFT or SUBSTRING function"

  

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

Run below query to see what is the physical file name of master database, was it changed from default master.mdf to some thing else
select * from master.dbo.sysaltfiles where dbid=1

If master database files are renamed from default names to something else, then you may try the below steps

>>stop sql server and renamed master log and data file to the original names. update the start up parameters with the new names in configuration manager
>>remove trace flag 902 , restarted sql

Hope this helps

Hello,

code gives the same error.

SELECT returns

1    1    760    -1    10    1048578    0    1    master    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
2    0    12768    -1    10    1048642    0    1    mastlog    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

both files are there where path shows.

I am sorry, small change to the script. please run below and see if it gives any error

   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')

- Also, please verify if there is any system database file which is different than normal

select * from master.dbo.sysaltfiles where dbid<5

So, basically SQL is trying to run the scripts from the 'msdb110_upgrade.sql' and is failing. There must be some statement in this file which is resulting in this error.

Script returns the same error.

System databases:

1    1    760    -1    10    1048578    0    1    master        C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
2    0    12768    -1    10    1048642    0    1    mastlog        C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
1    1    1024    -1    10    1048578    0    2    tempdev        C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
2    0    64    -1    10    1048642    0    2    templog        C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\templog.ldf
1    1    288    -1    128    2    0    3    modeldev    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\model.mdf
2    0    4464    -1    10    1048642    0    3    modellog    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\modellog.ldf
1    1    28936    -1    10    1048578    0    4    MSDBData    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf
2    0    3352    268435456    10    1048642    0    4    MSDBLog    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf

are all in place where path shows.

Before sending my message I've searched update script for "FROM master.sys.master_files" and I found script which you are showing me. I've made some digging and when I runned:

       SELECT physical_name
       FROM master.sys.master_files
       WHERE (name = N'master')

it returned also path to one my customers database name which has filename set correctly but it's Logical DB name is master and mastlog. I'm running shared hosting company and I allow my customers to restore their DBs from .bak files but I have no idea why this customers logical name is set to master and mastlog. Probably when I change this names update script will work as it should. On the other hand in my opinion there should be some kind of fail safe mechanism for situations like this one.

EDIT: After changing logical name for DB and log for mentioned DB your script runned fine so probably now when I remove T902 flag and restart my SQL Server update will run correctly. Big thanks for resolving this issue.

• Edited by Webio Saturday, December 15, 2012 11:07 AM
• Marked as answer by Webio Saturday, December 15, 2012 11:18 AM

Nice to know that the issue is identified and corrective actions are taken. Glad that I could help you

• Marked as answer by Webio Saturday, December 15, 2012 11:18 AM

For those who still might be receiving this error (i.e. their master files had not been renamed), let me tell you about my story and how I fixed this.

Being a consultant with many clients, I have various needs to keep backups of their master and msdb databases around.  Occasionally, I'll restore these databases to my local SQL server instance.  I restore them under a different name; some like "client1_master".  The first query that V supplied did indeed retun the invalid length error.  The file names in the second query exactly matech the files of my master database.

My next step was to execute this:
select * from master.sys.master_files where name = 'master'

query results below:

database_id   file_id   name      physical_name 
1                  1          master     C:\Prog....\....ERVER\MSSQL\DATA\master.mdf
16                1          master     C:\Prog....\....ERVER\MSSQL\DATA\client1_master_Data.mdf

This query returned two records!  One for my master database, the other for a master database I restored as a different name (I was troubleshooting a problem with the clients db server). 

When I restored the client's master database, I did not change the logical name.  The charindex function in the first query was failing becuase the text "master_data" was not included in the physical file name of the client's restored master db.  Because I had the DB server started under trace flag 902, I could not modify the logical file names of the client's databases, so I had to drop them.  But after doing that, the server instance started up.

Anyway, I wanted to put this out there as another reason this error could crop up.  From now on, I'll make sure any system database I restore to a "renamed" database will also rename the logical files.

Thanks V, for pointing me in the right direction.

Greg

This exactly what has happend to me. I also had another database called master. After changing it's name update script has executed how it should. If you want you can add your voice and comment here:

https://connect.microsoft.com/SQLServer/feedback/details/774535/error-537-severity-16-state-3-error-during-updating-master-db-from-2012-to-2012-sp1

I've descrived my issue on this CONNECT page.

This was incredibly helpful for me, thank you!

I had this problem because a previous DBA created databases named master2 and msdb2.  That would have been OK except their logical file names were master and msdb.  That blew up the upgrade script.  The fix was just to drop those two databases.

This happened to me.