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://darkslategrey-bat-805937.hostingersite.com)
