Compartir a través de

An interesting issue with SQL Server Script upgrade mode

  • Artículo

Here's another common issue that I've seen quite a few people run into of late.

When you run a patch against SQL Server, the patch installs successfully, but on restart, SQL goes into "script upgrade mode" and you're unable to connect to it. Upon looking at the errorlog, you see something like this:

2012-08-23 03:43:38.29 spid7s Error: 5133, Severity: 16, State: 1.

2012-08-23 03:43:38.29 spid7s Directory lookup for the file "D:SQLDatatemp_MS_AgentSigningCertificate_database.mdf" failed with the operating system error 2(The system cannot find the file specified.).

2012-08-23 03:43:38.29 spid7s Error: 1802, Severity: 16, State: 1.

2012-08-23 03:43:38.29 spid7s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

2012-08-23 03:43:38.31 spid7s Error: 912, Severity: 21, State: 2.

2012-08-23 03:43:38.31 spid7s Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.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.

2012-08-23 03:43:38.31 spid7s Error: 3417, Severity: 21, State: 3.

2012-08-23 03:43:38.31 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.

Script upgrade means that when SQL is restarted for the first time after the application of the patch, the upgrade scripts are run against each system db (to upgrade the system tables, views, etc. ). During this process, SQL Server attempts to create this mdf file in the default data location, and if the path is not available, then we get this error. Most of the time, it's a result of the data having been moved to a different folder, and the original Default Data path being no longer available.

The default data path can be checked from the following registry key (for a default SQL 2008 instance):

HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLServer

The Mssqlserver key will have a string entry named "DefaultData". If you see a location here that's no longer available, please change it to the current data location (alternatively, you can also "recreate" the default data path mentioned in the string value).

If you do not see the key, check for the please check the HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10.<instance name>Setup key, and see if you can spot the SQLDataRoot key there. Check to see if this key has the path mentioned above, and if so, update it to the current path.

If the path is correct, then one of the following conditions holds true:

1. The relevant drive is not added as a resource to the SQL Server group in Failover cluster manager.

2. The SQL Server resource does not have a dependency on the specified drive.

After this, restart SQL Server and the script upgrade should complete successfully this time. Hope this helps.

Comments

  • Anonymous
    January 28, 2014
    The comment has been removed
  • Anonymous
    February 10, 2014
    The comment has been removed
  • Anonymous
    February 26, 2014
    the master database is in Single-User-Mode,Every attempt (via GUI or SQLCMD) to switch back to Multi-User-Mode is resulting in error message "Option 'MULTI_USER' cannot be set in database 'master'. (Microsoft SQL Server, Error: 5058). no issues while connecting to the SQL server ,could you please help on this. select name,state_desc,user_access_desc from sys.databases

name state_desc user_access_desc master ONLINE SINGLE_USER sp_helpdb-----output

name db_size owner dbid created status compatibility_level master      5.00 MB sa 1 Apr  8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=655, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 100 Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul  9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)