SQL Server 2008 ServicePack 3 Troubleshooting

ServicePack 3 for SQL 2008 hit our update queue and it's been a great time.

I have a server running three (luckily "disposable") instances and while two of them upgraded without issue there was one in particular that fought the installation like a banshee.

After the automated SQL 2008 SP3 upgrade the instance would not start.  Users were greeted with "Server is in script upgrade mode. Only administrator can connect at this time."

Re-applying the update didn't help.  Restarting the service didn't help.

Googling the hell out of the right error messages helped a lot. 

Here's what I found:

Backups
Here's the standard disclaimer.  Stash your backups.  For throwaway instances ours are only retained for like three days.  So I moved mine elsewhere.  If you're lucky enough to be able to connect via DAC or other method be sure not to break the log chain (i.e. making a full backup and in doing so invalidating any previously made Transaction Log backups).

Finding the "good" error logs
The Windows Event Viewer was barren, so being familiar with the location of my ERRORLOG file was invaluable.

Mine was here (for named instance "BOYERSQL01"):  ...Program Files\Microsoft SQL Server\MSSQL10.BOYERQL01\MSSQL\Log

Errors
ERRORLOG yielded several helpful errors and I was able to determine where the update was failing.

• Error #1 "'sqlagent100_msdb_upgrade.sql' encountered error 5597"
  Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered
  error 5597, state 1, 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.

I suspect Error #1 was caused by us moving datafiles around.  You can correct this with a bit of registry diving.  The default log and data locations are found here:  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.BOYERSQL01\MSSQLServer

You'll need "DefaultData" and "DefaultLog" REG_SZ keys that point to your System databases' Data and Log file locations respectively.  After creating or correcting these entries restart your affected instance and re-run the update.  (SP3 is found here.)

• Error #2 "FILESTREAM feature could not be initialized"
  Msg 5597, Level 16, State 1, Server MYSERVER\BOYERSQL01, Line 1
  FILESTREAM feature could not be initialized. The Windows Administrator must enable FILESTREAM on the instance using Configuration Manager before enabling through sp_configure.

We're not actively using FILESTREAM on this instance, so I had leeway here.  I cleared this error by changing the value of the following registry key to 0:  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQLSERVER\FileStream.  Then I restarted the SQL Server service corresponding to this instance via the services.msc snap-in.

Accessing the database when there's no accessing the database:
Don't forget about the command-line.  Using the following command I can start the instance with limited features, single user, and run queries (no semicolon suffix, put GO on a line by itself to execute):
"C:\Program Files\Microsoft SQL Server\MSSQL10.BOYERSQL01\MSSQL\Binn\sqlservr.exe" -sBOYERSQL01 -c -f

Here's a quick cheat sheet for valid parameters:
 -f starts instance with minimal configuration (excludes c2 auditing mode) - by default, single user mode
 -c reduces time needed to start SQL Server from the command prompt
 -s specifies a named instance
 -n prevents the use of the Application Log to store SQL Server events
 -m specifies single user mode and bypasses any shutdowns caused by audit failures
 -x turns off monitoring features like perfmon counters

Patience
If your ERRORLOG is clear of the errors mentioned above for 20 minutes you're probably okay to start connecting again.  Give it time and use a functional database's ERRORLOG as a point of reference.

Still having issues?  I've included links that provide additional help.  Also you can always Repair/Reinstall your SQL database installation, but after reading through the cryptic ERRORLOG warnings about recovering the MASTER database which turned out to be exaggerated  I'd do this as a last resort.

I have no affiliation with the Links included below:

http://dbadiary.wordpress.com/tag/script-level-upgrade-for-database-master-failed-because-upgrade-step-sqlagent100_msdb_upgrade-sql-encountered-error-598/

http://blogs.msdn.com/b/karthick_pk/archive/2010/11/18/sqlserver2008-script-level-upgrade-for-database-master-failed-because-upgrade-step-sqlagent100-msdb-upgrade-sql-encountered-error-574-state-0-severity-16.aspx

http://sql-articles.com/index.php?page=articles/msysdb.htm

http://support.microsoft.com/kb/224071