I have done countless SQL Server upgrades and rarely have issues after applying a Service Pack (SP) in terms of SQL Server and/or database uptime. However, here is one example where I ran into a serious issue with the master database after an SP4 upgrade on SQL 2012. This was one of the production SQL Servers, and the database engine did not come online after the SP4 upgrade. The impact was critical as 20 application databases were hosted on the same database server.
The process of patching is simple, and even simpler when the database server is standalone. I ran the SQL2012 SP4 setup, clicked Next a few times, and clicked Install. After few minutes the setup program reported a failure.
I followed the basic troubleshooting approach and checked the SQL bootstrap log file. However, SQLservr.dll seemed updated with SP4 build. Later I checked the SQL Server errorlog file that had updated information regarding Service Pack. A portion of the log is shown below:
2020-04-22 00:30:42.37 Server Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) . . . 2020-04-22 00:30:43.39 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required. 2020-04-22 00:30:43.40 Server Software Usage Metrics is disabled. 2020-04-22 00:30:43.40 spid3s Starting up database 'master'. 2020-04-22 00:30:43.52 spid3s 36 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required. 2020-04-22 00:30:43.70 spid3s 0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required. 2020-04-22 00:30:43.70 spid3s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required. 2020-04-22 00:30:44.00 Server CLR version v4.0.30319 loaded. 2020-04-22 00:30:44.23 Server Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:. . . 2020-04-22 00:30:47.98 Logon Login failed for user 'uni'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 10.128.116.14] 2020-04-22 00:30:47.99 Logon Error: 18401, Severity: 14, State:1. 2020-04-22 00:30:47.99 Logon Login failed for user ‘uni’. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 10.128.116.6] 2018-08-29 00:37:04.22 spid3s 2018-08-29 00:37:04.22 spid3s Starting execution of MSDB_POST_INSTALL.SQL 2018-08-29 00:37:04.22 spid3s 2020-04-22 00:37:04.22 spid3s 2020-04-22 00:37:04.22 spid3s Starting execution of MSDB_POST_INSTALL.SQL 2020-04-22 00:37:04.22 spid3s 2020-04-22 00:37:04.25 Logon Error: 18401, Severity: 14, State:1. 2020-04-22 00:37:04.25 Logon Login failed for user 'uni'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 10.128.97.8] 2020-04-22 00:37:04.69 Logon Error: 18401, Severity: 14, State:1. 2020-04-22 00:37:04.69 Logon Login failed for user 'usr'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 155.250.66.60] 2020-04-22 00:37:05.02 Logon Error: 18456, Severity: 14, State:5. 2020-04-22 00:37:05.02 Logon Login failed for user 'SQL\ILEARNSQL'. Reason: Could not find a login matching the name provided. [CLIENT: 10.150.7.168] 2020-04-22 00:37:05.55 spid3s Signing sps ... 2020-04-22 00:37:05.55 spid3s Dropping existing Agent certificate ... 2020-04-22 00:37:05.55 spid3s DBCC TRACEON 4606, server process ID (SPID) 3. This is an informational message only; no user action is required. 2020-04-22 00:37:05.55 spid3s DBCC execution completed. If DBCC printed error messages, contact your system administrator. 2020-04-22 00:37:05.63 spid3s DBCC TRACEOFF 4606, server process ID (SPID) 3. This is an informational message only; no user action is required. 2020-04-22 00:37:05.63 spid3s DBCC execution completed. If DBCC printed error messages, contact your system administrator. 2020-04-22 00:37:05.63 spid3s Error: 537, Severity: 16, State:3. 2020-04-22 00:37:05.63 spid3s Invalid length parameter passed to the LEFT or SUBSTRING function. 2020-04-22 00:37:05.64 spid3s Error: 912, Severity: 21, State:2. 2020-04-22 00:37:05.64 spid3s 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. 2020-04-22 00:37:05.64 spid3s Error: 3417, Severity: 21, State:3. 2020-04-22 00:37:05.64 spid3s 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. 2020-04-22 00:37:05.64 spid3s SQL Server shutdown has been initiated 2020-04-22 00:37:05.64 spid3s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
In the log I saw two type of errors along with a note about rolling forward transactions in the master database. One error was “login failed for user database Reason: Server is in script upgrade mode”. The other error was with the master database and said “Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 537, state 3, severity 16.”
The first error did not seem too important to me because the SQL Server engine was not accessible. The main issue was with the master database and the failure to execute “MSDB_POST_INSTALL.SQL”. So, I believe this would be easy if I execute this SQL statement. It should work or give another error. But how and where to execute the script? The database engine is not accessible.
DO NOT think of restoring, repairing or rebuilding the master database at this point as per the above error message, you might end up causing more issues for the instance.
SQL Server has an important trace flag, 902, which bypasses the applied patch SP or CU. I used this trace flag to bypass the applied patch and allow SQL Server to start. Then I executed that script that was creating issue during the setup on the master database.
For more detailed information on trace flag, you can read this link: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017
WARNING: This trace flag is for troubleshooting of failed updates during script upgrade mode. Do not continuously use it in a production environment as it is not supported by Microsoft. Database upgrade scripts need to execute successfully for a complete install of Cumulative Updates and Service Packs. Not doing so can cause unexpected issues with your SQL Server instance.
To fix this issue, I first added a start up parameter for trace flag 902 and brought the SQL Server database engine online. I next opened a new query in SSMS with the script, MSDB_POST_INSTALL.SQL script. I executed it, but it failed with error below:
Msg 537, Level 16, State 3, Line 98 Invalid length parameter passed to the LEFT or SUBSTRING function. 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')
Next I ran this code:
SELECT * FROM sys.sys.master_files WHERE name = ‘master’;
The results had two entries for the same logical name ‘master’. One of these was for the master database and another for a user database: masterdatabase.
database_id file_id file_guid type type_desc data_space_id name physical_name state state_desc 1 1 NULL 0 ROWS 1 master D:\MSSQLSERVER\MSSQL11.SQL\MSSQL\DATA\master.mdf 0 ONLINE 50 1 NULL 0 ROWS 1 master G:\\masterdatabase.mdf 0 ONLINE
I next renamed the logical name of masterdatabase (the user database) using this SQL statement.
USE [master]; GO ALTER DATABASE masterdatabase MODIFY FILE ( NAME = master, NEWNAME = master_Data ); GO
when I ran the query against sys.sys.master_files again, it showed one entry.
I again executed the MSDB_POST_INSTALL.SQL script and it ran to competion with no errors. At this point I removed the 902 trace flag from the startup parameters and restarted the SQL Server database engine. The startup process started the script upgrade and ran other scripts. These all completed successfully and SQL Server was restarted automatically.
After the restart, the SQL Server database engine was upgraded successfully to SQL Server 2012 SP4. The error log looked clean without any errors.
Conclusion
In my case, a user database, masterdatabase, had a data file with a logical name the same as the system database, master. This caused the issue, and I learned to ensure that user databases use logical names other than those in use by system database.
Also, it is important to know that trace flag 902 bypassed recently applied patches. This trace flag is for troubleshooting of failed updates during upgrades.
