In this article I want to show you one tricky issue I faced recently. This took around 6 hours to track down what exactly was happening and hence I am sharing the details so that if you have the same problem it will be a quick check for you instead of hours.

My client environment is not totally upgraded to SQL Server 2005, and I still have a few SQL Server 2000 servers in my environment. On one such SQL Server 2000 server I received the request to install SP4. SP4, which is usually a cakewalk to install, failed. I started to investigate the failure, and here are the details of the issue and its resolution:

As usual I started the SP4 install after doing the below pre-requisite checks:

1. I rebooted both the cluster nodes as SP4 was tried earlier by one of my team members. This step also ensures that there are no terminal services connection open to the cluster nodes. This is one of the reasons why SQL setups/Sp/hotfix fails on the clusters.
2. Changed the environment variables TMP and TEMP to C:\Temp on both the nodes.
3. Stopped the following services :
   • Antivirus
   • SNMP Service
   • Any monitoring software related Services

But it didn't help. Setup still failed with the error: Setup failed to perform required operations on the cluster nodes

I executed 'select @@version' and it returned 8.00.2039 but when I checked the sqlsp.log( in c:\winnt directory), it has the message "installation failed"

The last few lines from the log are:

21:58:29 C:\SQL2KSP4\x86\BINN\osql.exe -Slpc:Servername-b -n -d master -o "C:\Program Files\Microsoft SQL Server\MSSQL\install\sp1_serv_uni.out" -i "C:\Program Files\Microsoft SQL Server\MSSQL\install\sp1_serv_uni.sql" -E
21:58:29 Process Exit Code: (1)
21:58:29 Error running script: sp1_serv_uni.sql (1)
21:58:29 c:\temp\1\SqlSetup\Bin\scm.exe -Silent 1 -Action 6 -Service MSSQLSERVER
21:58:44 Process Exit Code: (0)
21:58:44 Action CleanUpInstall:
21:58:44 Installation Failed.

And the specified output file above(sp1_serv_uni.out) had the message:

[DBNETLIB]General network error. Check your network documentation.
[DBNETLIB]ConnectionRead (recv()).

So it was confirmed that setup is failing to connect to SQL Server for applying the service pack, but it wasn't clear why. None of the other logs helped, but with some searches on the Internet, I found one similar issue.

When setup is applying SP4, it starts SQL Server in single user mode and if any application services exist on the server that continuously try connecting to SQL, they may get that single connection and may prevent setup from connecting. This sounded correct to me, and I stopped the application services on the cluster. Luckily in my setup I have them as a group in the cluster, so I kept that group offline easily.

Then I retried the Service Pack setup, but it didn't solve the problem. After some investigation again, I found a few entries in the event log that pointed to the correct problem:

17658 :
SQL Server started in single user mode. Updates allowed to system catalogs.
......
SuperSocket info: ConnectionListen(Shared-Memory (LPC)) : Error 0.
SuperSocket info: (SpnRegister) : Error 8344.
......
19013 :
SQL server listening on TCP, Named Pipes.
17052 :
Recovery complete.
17148 :
SQL Server is terminating due to 'stop' request from Service Control Manager.
SQLSETUP: The installation of SQL 2000 Service Pack has been aborted.

The sentences above in italics pointed that there is some shared memory problem. After that the sentence "sql server is listening on TCP, named pipes" confirmed that shared memory is not working. If we remember correctly the last few lines in the sqlsp.log (pasted earlier), they indicated that setup is trying to connect with shared memory. The line from the log is pasted below again for reference:

21:58:29 C:\SQL2KSP4\x86\BINN\osql.exe -Slpc:Servername-b -n -d master -o "C:\Program Files\Microsoft SQL 

I then started checking the issue with shared memory and found one known issue with it. If we try changing the SQL service account from services console instead of Enterprise manager, one place the account information doesn't get updated is on the registry key:

HKLM\Software\Microsoft\MSSQLSESRVER\MSSQLServer \Supersocketnetlib\LPC

This key didnt have any accounts added to it in the permissions window. So added the Servername\Administrators group here and gave it full control. I performed the same action on the second node also and then restarted the SQL service. Now I see the message:

19013 :
SQL server listening on TCP, Shared Memory, Named Pipes.

At this point I then restarted setup and it went successfully.

Hopefully if you have issues with SP4, this article will help you save time installing the Service Pack.

Here are some sites I referred in the process:

http://kbupdate.info/sql-server-2000-2007-06.php

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

http://www.sqlservercentral.com/Forums/Topic294262-48-1.aspx#bm297595

http://weblogs.asp.net/steveschofield/archive/2004/06/09/151588.aspx

http://www.eggheadcafe.com/forumarchives/SQLServersetup/Jul2005/post23409196.asp