SQL Server Instance in Script Upgrade mode

Introduction:

Almost all of us in our DBA life would have patch/upgrades SQL Server instance and would have faced many issues while upgrading the SQL Server build. Today I will discuss in little detail about the background of the Script Upgrade Mode and the fix.

What is Script Upgrade mode:

When we apply a patch on SQL Server instance, the patching upgrades the system databases (views,tables) immediately after the restart of the SQL Server Services( Stopping of the services and starting it again is part of patching process). There are certain scripts (most of the times sqlagentxxx_msdb_upgrade.sql) in the patch/SP which is applied only after the SQL Server service starts. During the Process of applying these scripts if anyone tries to connect to SQL Server instance then he/she will get the below error message.

Login failed for user ‘LoginName’ :Reason: Server is in script upgrade mode.
Only administrator can connect at this time.

Scenarios and Fix:

1)      Wait for few more minutes after start of SQL Server Services, as it takes a little time to apply the script .Keep an eye on any error message in Windows Eventlog and SQL Server Errorlog. If everything is good then you would be able to connect.

2)       In one of the scenario the default location of the database was changed since its installation but the registry entry was still pointing to the default path folder whose structure was deleted from the disk. In this case you will get below error logged in the Errorlog

2013-10-29 11:07:44.01 spid7s Error: 5133, Severity: 16, State: 1.
2013-10-30 11:07:44.01 spid7s Directory lookup for the file "D:\SQLDATA\temp_MS_AgentSigningCertificate_database.mdf" failed with the operating system error 3(The system cannot find the path specified.).
2013-10-29 11:07:44.01 spid7s Error: 1802, Severity: 16, State: 1.
2013-10-29 11:07:44.01 spid7s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2013-10-29 11:07:44.01 spid7s Error: 912, Severity: 21, State: 2.
2013-10-29 11:07:44.01 spid7s Script level upgrade for database 'master' failed because upgrade step 'sqlagent90_sysdbupg.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.
2013-10-29 11:07:44.02 spid7s Error: 3417, Severity: 21, State: 3.
2013-10-29 11:07:44.02 spid7s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.

Here SQL Server is picking up the default data drive location from Registry and trying to create temporary .mdf file which is necessary to complete the upgrade.

FIX:

To quickly resolve this issue we need to look into the registry path containing the default data path location and change it to existing one. This approach is more practical as we will not face the same issue while next patching activity otherwise you can create the missing folder structure on the drive.

Registry path for default instance of SQL 2008:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer

Check the value for the String Entry named “DefaultData” and change the location to existing one .Restart the SQL Services and monitor Errorlog file.

3)      Another scenario is when you have Utility control Point enabled in SQL Server 2008 R2 and Agent XPs disabled. This issue is fixed in SQL Server 2008 R2 CU2 but I included this here if in case this is the one you are facing.

This component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, see “Surface Area Configuration” in SQL Server Books Online.

FIX:

Below are the steps you need to follow in order to fix above error message,

1. Enable Trace Flag 902 to disable the script execution. Restart the SQL Services.
2. Make sure that SQL Agent Services are in stopped state.
3. Connect to SSMS and execute below commands

           EXEC sp_configure ‘show advanced’, 1;

          RECONFIGURE;

         EXEC sp_configure ‘allow updates’, 0; 

         RECONFIGURE; 

         EXEC sp_configure ‘Agent XPs’, 1; 

        RECONFIGURE; 

        GO

4)      Stop SQL Services and remove the trace flag 902. Start the services.

I have tried to cover a few scenarios which I have faced in the past, however, there are many possible causes. Please post your ErrorLog here if you are facing this issue with a different error message.