What do I need to know about SQL Server 2000 SP4?

Before you install 
 
Make sure you do all of your reading beforehand, including the links offered in Article #2531. 
 
Before downloading SP4, be sure you understand what package you are installing and what products you have installed. 
 
If you have the 32-bit SQL Server engine or client tools installed, you want Sql2k32sp4.exe. 
 
If you have the Desktop Engine (MSDE) installed, you want Sql2kdesksp4.exe. 
 
If you have Analysis Services installed, you want Sql2k32asp4.exe. 
 
If you are running the 64-bit edition of SQL Server, you want Sql2k64sp4.exe. 
 
Make sure none of your databases are marked suspect (particularly the system databases: master, msdb, model, tempdb). If you try to install SP4 in this scenario, you may leave your server in an unusable state. So you will want to sort out the suspect problem *before* installing SP4, as opposed to attempting to use SP4 to fix it. 
 
If you have backups or restores going on, or you are using backup strategies that involve a warm standby, or you have user databases that are suspect or in other non-default states, you will get a warning dialog that some user databases are not writable. In my case, I had a warm standby, and I proceeded with installation and decided to recycle each warm standby completely (e.g. start over with a fresh full backup coming out of SP4). In the case of backup or restore activity, I would wait until it is complete and suspend them until after the server comes back up. 
 
I suggest that you have the latest version of MDAC installed prior to installing the service pack. While the service pack does include a version of MDAC, it may not be the most recent one available when you ugrade. See Article #2057 and MDAC Download Page for determining and upgrading your existing version. 
 
Shut down any external programs that may be utilizing a shared memory connection to SQL Server, such as Reporting Services, Sharepoint Portal Server, Windows Update Services, Microsoft Operations Manager, any instances of SQL Server / MSDE, etc. See KB #891085 for details. 
 
Ensure that Named Pipes, TCP/IP and Enable Shared Memory Protocol are on under Client Network Utility. I realize you may have some of these options disabled for security purposes, but they are needed for the SP install. So, after the SP is installed, you can disable them again. There is no KB article to go with this, not sure if that's because PSS hasn't encountered the issue, or that it's so basic that an MVP should have spotted it when they first started having the problem, not 8 days later... 
 

---

 
While installing 
 
If you get errors when running replcom.sql, this is going to sound weird, but disable or remove SQL LiteSpeed's command substition feature, if it is installed. This feature intercepts all backup commands (including those in the sp_instdist script) and replaces them with the LiteSpeed equivalents, and this causes the script to fail. (I think this would fall under the category of third-party monitoring software, mentioned briefly in KB #811168.) 
 
And rather than repeat issues that may be repeating from SP3 / SP3a, be sure to check Article #2440 for any issues you may be having during install (many of these scenarios can occur during any service pack install). 
 

---

 
If you are running a cluster 
 
Note that you do not have to install the service packs on all nodes, only the currently active node, but a failover will be required.  
 
Make sure Windows Task scheduler is enabled on all nodes. (See this blog post for more details.) 
 
Read the following articles:  
 
Install on a Failover Cluster 
 
KB #811168 SQL Server 2000 Service Pack 3, Service Pack 3a, or Service Pack 4 pre-installation checklist for SQL Server 2000 virtual server for failover clustering 
 
KB #254321 Clustered SQL Server do's, don'ts, and basic warnings  
 
Fix your path(s) for temporary files, for the user running setup, the user SQL Server runs as, and the user the cluster service runs as. See KB #814844 for more information. 
 
Make sure that the user running setup, the user SQL Server runs as, and the user the cluster service runs as belongs to the local administrators group on all nodes. The user running setup must be a domain user, because he is authenticated via Windows Auth when aplying changes to the other nodes. 
 

---

 
Issues after install 
 

• processes may seem to be blocking themselves, according to sysprocesses or sp_who/sp_who2. Don't be alarmed; this is normal behavior, SP4 is just a bit more comprehensive about reporting things like latch waits. Again, this is not a change in the engine's behavior, just a change in the way SQL Server displays the behavior to you. See KB #906344 and this Google thread for more information. 
   
  
• you may have lost your ability to use memory under AWE. The initial release of SP4 (8.00.2039) had an artificial memory inhibitor, which would limit SQL Server on large memory systems to 2 GB. I am still not clear how this got into production, but there is a hotfix for it, labeled 8.00.2040. In all of my SP4 upgrades, I immediately installed the hotfix. To obtain the hotfix, visit KB #899761. After this, you should install the 2187 cumulative hotfix, available at KB #916287. This is used to correct major issues with BULK INSERT and to ensure correct installation on all nodes in a cluster. 
   
  
• linked servers to other vendors may start failing. For example, if you are using the Oracle 10g client, you can try reinstalling the Oracle 10g client, which should reset anything that SP4 may have changed... you should also check your vendor for updates to their packages. 
   
  
• connection strings may no longer be valid. Somewhere between the MDAC and SP4 updates, connection strings are a bit more strict. There are two styles for OLEDB: 
   
  

  Conn.Open "provider=SQLOLEDB.1; server=?; database=?; uid=?; pwd=?;"  Conn.Open "provider=SQLOLEDB.1; data source=?; initial catalog=?; user id=?; password=?;"

   
  While it requires more typing, I strongly suggest the latter. And I highly recommend you do not mix these methods, e.g. do not use server with initial catalog or data source with uid. It may work in most scenarios, but I have seen it fail. (You may also try dropping the .1 from the first parameter, if the rest of this doesn't help.) 
   
  
• transaction log backups that worked fine before SP4 may slow down considerably, or fail with an error like: 
   
  

  Executed as user: domain\user. There is insufficient system memory to run this query.  [SQLSTATE 42000] (Error 701) BACKUP LOG is terminating abnormally.  [SQLSTATE 42000] (Error 3013).  The step failed.

   
  There is an undocumented BACKUP argument called MaxTransferSize, which can allow you to override the default 1MB size of contiguous blocks of virtual memory used for the backup. You can try using 262144 (256K) or 524288 (512K). See KB #904804 for more information. 
   
  
• you may experience performance degradation, or output changes, in queries that involve decimal or numeric columns with different precision/scale. See KB #899976 for more information (including a trace flag that will allow you to revert to SP3 behavior in data type precedence evaluation). 
   
  
• if you install MSDE SP3a onto a machine that already has Service Pack 4 installed on another instance, there may be a registry key corruption: 
   
  

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup    SQLPath = C:\Program Files\Microsoft SQL Server\80\Tools

   
  MSDE SP3a will truncate this path, removing the trailing "s," and this prevents existing shortcuts to Enterprise Manager, Query Analyzer and SQL Profiler, among others, from working correctly. You can manually fix the registry key using regedit to get around this problem. You should replace your MSDE SP3a installation packages with the SP4 version, available from the SP4 page. 
   
  
• you may receive a buffer overrun when using SQLDiag.exe. This issue has been pointed out to PSS, so hopefully you are using this in relation to a case / SRX#, and PSS will help you get SQLDiag running. 
   
  
• you will see different behavior in DBCC MEMORYSTATUS; the second resultset now has 7 rows instead of 6, with a new row labeled 'visible.' This number refers to the actual number of buffers concurrently visible, except in the case of AWE where the buffers are in "hidden" memory (above 4GB). There should have been an update by now to reflect this in KB #271624, but it remains unchanged.

---

 
As always 
 
If you have come across an issue that is not listed here, please let us know.