SQL Server 2012 SP1 Upgrade Step ‘msdb110_upgrade.sql’ Encountered Error 537, State 3, Severity 16

SQL Server 2012 instance does not start after applying SP1. Setup finished successfully, but SQL instance failed to start. Reviewed SQL Errorlog file and noticed below errors at the end of the file where it failed applying the upgrade scripts.

spid8s Error: 537, Severity: 16, State: 3.
spid8s Invalid length parameter passed to the LEFT or SUBSTRING function.
spid8s Error: 912, Severity: 21, State: 2.
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.
spid8s Error: 3417, Severity: 21, State: 3.
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.

This error Invalid length parameter passed to the LEFT or SUBSTRING function. will occur if there are any user database logical file names in the instance matches the master or mastlog.

Start SQL Server using -T902 Trace Flag:
To run any commands, we first need to connect to the SQL Server instance, but the SQL Services are in stopped state. We can start the SQL Server by skipping the script upgrade using Trace Flag -T902. Please refer below on how to start SQL Server instance using -T902

How to Start SQL Server Using -T902 Trace Flag to Skip the Script Upgrade

Run below command to find if there are any database logical file names similar to Master database logical file names.

select db_name(database_id) as DatabaseName, name, Physical_name from master.sys.master_files where name like 'mast%'

If above query returns any other user database having logical names are master or mastlog, then either detach that database or rename the logical file names to some other name.

To Detach a Database
Use Master
Go
EXEC SP_Detach_DB @dbname = N'SpecifyDatabaseName'

To Rename Logical File Names
Use Master
Go
ALTER DATABASE MODIFY FILE ( NAME = 'current_logical_name', NEWNAME = 'new_logical_name')

Now start the SQL Server instance by removing Trace Flag -T902. Check SQL Errorlog to ensure the upgrade scripts completed successfully.

Cheers,
Keerthi Deep

 

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *