Saturday
Jul112015

SQL 2014 SP1

nameOfKbArticleDownload.exe -x

 ...will extract your setup files.

Then

setup.exe /QUIETSIMPLE /ALLINSTANCES

...will install.

Worked well for me for minimal server interface ("core" or "limited" GUI) OS install.

Thursday
May072015

SQL Server 2008 ServicePack 4 Installation Troubleshooting

My ServicePack 4 for SQL 2008 installation kept disappearing without leaving any logs behind.

The lone problematic cluster node was also reluctant to rejoin the cluster – insisting that it already belonged to one.

In addition to the KB installation routine disappearing the problem was marked by sluggish performance, especially when opening up PowerShell, and this error in the Application log:

.NET Runtime version 2.0.50727.5485 - Error 'Invalid syntax on line 166.' occurred while parsing the 'Machine' policy level. The default policy level was used instead.

Had to reset the .NET security policy to resolve:

C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\caspol.exe -machine –reset

Forcing the node back into the cluster was, thankfully, pretty simple:

Cluster node %COMPUTERNAME% /forcecleanup

Turns out you can extract the files to run the setup.exe directly from the patch which helps with troubleshooting by creating more verbose logs:

SQLServer2008SP4-KB2979596-x64-ENU.exe -x

Running setup.exe will spin up a setup100.exe thread that you can keep an eye on

The logs are stashed at the following locations:

%TEMP%

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log

In the course of troubleshooting we verified the following permissions were indeed held by the service account running the patch install:

  • Log on as a service(SeServiceLogonRight)
  • Replace a process-level token (SeAssignPrimaryTokenPrivilege)
  • Bypass traverse checking (SeChangeNotifyPrivilege)
  • Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

Friday
Nov182011

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