Wednesday, August 29, 2012

SQL Server Service crash after Service Pack Install

A tough lesson regarding why you should always keep the configured default Data and Log paths updated in SQL Server.

At my company, our virtual infrastructure is growing and changing rapidly.  As a result the configuration of our SQL Servers is changing also.  This results in additional drives and drive letter changes for our SQL servers.

Unfortunately, if the default Data and Log paths are not updated when the drive letters change, there will be errors after installing (or uninstalling) a Service Pack or Cumulative Update.  The GUI portion of the install will complete successfully, but there are updates that occur on the initial service start after the GUI portion of the install is complete.  One update that occurs that that initial service start is database upgrades.  If your default paths for data and log files are not correct (in my case the drives referenced no longer existed), the upgrade of the master database will fail and the SQL Server service will crash.  

In a production setting, this can induce fear, panic, and mass hysteria (or at the very least mass Google searching, which is likely how you arrived at this page to begin with).  However, the solution is pretty simple as the default paths are stored in the Windows registry.

Launch Registry Editor (regedit.exe) and navigate to the following section of the registry tree: 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\{SQL Version}\MSSQLServer

Note: If you are running a 32-bit version of SQL Server on a 64-bit OS, you'll need to navigate to the Wow6432Node tree (HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\{SQL Version}\MSSQLServer)

SQL 2000 {SQL Version} = MSSQLServer
SQL 2005 {SQL Version} = MSSQL.1
SQL 2008 {SQL Version} MSSQL10.MSSQLSERVER
SQL 2008 R2 {SQL Version} = MSSQL10_50.MSSQLSERVER
SQL 2012 {SQL Version} = MSSQL11.MSSQLSERVER

Then update the paths specified in the following keys:
DefaultData
DefaultLogs

Once the paths are correct, the master database upgrade will complete successfully (as SQL Server will be able to create the temporary files in the default paths) and once all other system and user DBs are upgraded you should be back up and running.  

Note: The database upgrades will take some time if you have a lot of user DBs, but you can monitor the ERRORLOG to observe the progress.

Here is the appropriate snippet from my log for the failure on the data file:
2012-08-29 08:45:43.39 spid7s      Error: 5133, Severity: 16, State: 1.
2012-08-29 08:45:43.39 spid7s      Directory lookup for the file "G:\MSSQL\Data\temp_MS_AgentSigningCertificate_database.mdf" failed with the operating system error 3(The system cannot find the path specified.).
2012-08-29 08:45:43.39 spid7s      Error: 1802, Severity: 16, State: 1.
2012-08-29 08:45:43.39 spid7s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2012-08-29 08:45:43.39 spid7s      Error: 912, Severity: 21, State: 2.
2012-08-29 08:45:43.39 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-29 08:45:43.39 spid7s      Error: 3417, Severity: 21, State: 3.
2012-08-29 08:45:43.39 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.
2012-08-29 08:45:43.39 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

You'll see a similar error if the log folder is not set correctly:
2012-08-29 08:57:50.73 spid7s      CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'F:\MSSQL\Logs\temp_MS_AgentSigningCertificate_database_log.LDF'.
2012-08-29 08:57:50.93 spid7s      Error: 5123, Severity: 16, State: 1.
2012-08-29 08:57:50.93 spid7s      CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'F:\MSSQL\Logs\temp_MS_AgentSigningCertificate_database_log.LDF'.
2012-08-29 08:57:50.93 spid7s      Error: 1802, Severity: 16, State: 4.
2012-08-29 08:57:50.93 spid7s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2012-08-29 08:57:50.93 spid7s      Error: 912, Severity: 21, State: 2.
2012-08-29 08:57:50.93 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-29 08:57:50.93 spid7s      Error: 3417, Severity: 21, State: 3.
2012-08-29 08:57:50.93 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.
2012-08-29 08:57:50.93 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

No comments:

Post a Comment