SP3: To Install or Not to Install?

Microsoft released SQL Server 2000 Service Pack 3 (SP3) on January 17, raising the inevitable question, "To install or not to install?" SQL Server Product Support Services (PSS) recommends applying the latest service pack even if you're not aware of a specific fix that will help you. I'm aggressive about applying service packs because I hate to spend hours tracking down a problem only to find that the last service pack fixed it. However, some DBAs prefer to wait a few weeks and monitor the newsgroups to be sure that installing a new service pack won't create a problem. And some SQL Server shops that have high uptime requirements for their production systems might have to wait months before applying a service pack. A recent newsgroup posting said that one company took 8 months to test and deploy SP2 on one of its critical systems.

The "don't fix it if it ain't broke" school of thought is prudent when it comes to server maintenance. SQL Server service packs are especially tricky because you can't easily remove them. To remove SP3 (like SP1 and SP2), you have to uninstall SQL Server, then reinstall from the ground up. Needless to say, you should have a well-crafted recovery plan—including tested backups—before you apply a service pack to a production server. Like doctors, good system administrators should live by the creed "first do no harm" when making decisions that can affect the stability or availability of the systems they manage.

However, once you've installed SP3, you can take advantage of the upgrades it provides. Service packs are supposed to fix bugs, but SQL Server service packs also regularly add new features; SP3 is no exception. The readme.txt file's section 5.1, Database and Desktop Engine Enhancement, provides a complete and cumulative list of all the features that SQL Server 2000 service packs have added. One new SP3 function, fn_get_sql(), is particularly interesting. This function is similar to the existing DBCC INPUTBUFFER command. Both functionalities let you see the SQL statement that's being executed by a given SPID, but fn_get_sql() is more powerful. DBCC INPUTBUFFER shows only the first 255 characters of a SQL statement and shows only the SQL statement at the top of a nesting hierarchy. Fn_get_sql() lets you see the entire SQL statement that's being executed, even if it's nested within multiple layers of procedure calls. For example, if a procedure named Proc1 calls Proc2, DBCC doesn't let you see when Proc2 is running, whereas fn_get_sql() shows when Proc2 is being called.

Note that SP3's main download page announces that the service pack contains a "new monitoring API" with which "database administrators (DBAs) or third-party tools can diagnose problem processes." That item refers to fn_get_sql(), which is a great function but hardly a monitoring API. This misnomer has already caused some confusion and started discussions on the SQL Server newsgroups, with people searching for documentation about a new API that doesn't exist.

Experienced and junior DBAs alike need to read the readme.txt files for each service pack. Read them end to end—you'll almost always find a nugget of information that's relevant to your environment. In the case of SP3, you also need to read the addendum to the readme file to get all the readme information. Think of it as readme.txt Part 2.

Even if you don't immediately plan to apply SP3 to any of your servers, visit the SP3 download site. With SP3, Microsoft has released an updated version of SQL Server Books Online (BOL) and updated samples for SQL Server. You can find all the relevant download files, fix lists, and readme documents at http://www.microsoft.com/sql/downloads/2000/sp3.asp .

One final note: Please review the latest security posting about the SQL Slammer worm at http://www.microsoft.com/security/slammer.asp and update your systems accordingly. This newest bulletin describes a patch that fixes a SQL Server problem that Microsoft originally found and fixed last summer. The QL Slammer worm couldn't have spread so quickly over the weekend if SQL Server administrators—including some at Microsoft, according to news reports—had kept up with the latest security hot fixes. (For coverage of the SQL Slammer worm, see the News and Views section below.) Over the past few weeks, I've discussed the importance of adhering to best practices, and this unfortunate worm attack underscores the importance of making sure the basics are covered. I'll revisit the best-practice discussion in an upcoming issue of SQL Server Magazine UPDATE. But PLEASE review the Microsoft security bulletin right now.

Discuss this Article 2

Stephen Anderson (not verified)

on Feb 5, 2003

We are using a linked server to access a 7.0 from a 2000 server. Before upgrading to SP3 on the 2000 SQL server, we had a stored procedure that used the linked server and ran in less than 10 seconds. After the upgrade, the same procedure took between 22 and 30 minutes. We have rebuilt the link and the stored procedure and still have the issue. All our other procedures that use this link are experiencing the same issues.
Does anyone have a solution to this issue?

We install the SP3 on my Develop server (In this server are IIS, COM+ and SQL all toguether), but we found that this SP refresh the installation of MDAC 2.7, but I have COM components working with mdac 2.6 and all my COM+ applications cannot access the server. For fix the problem we use the special tool, but we cannot un-install the library. We need reinstall the server. Please if you have COM components using this library, do not update the mdac objects. First verify in other server after apply SP3. I check my SQL instalation (after SP3) from other COM+ server and works fine. The problem is when all is in the same server (IIS, COM+ and SQL) I hope someone will helpfull this experience.

From the Blogs

The quest for the Golden Record to achieve a single, accurate and complete version of a customer record is worth the pursuit to attain survivorship. Record matching and consolidation are only the beginning. Melissa Data takes a new approach. Learn how to apply intelligent rules based on reference data to make smarter and better decisions for data cleansing....More

On SQL Servers where Availability Groups (or Mirroring) isn’t in play, I typically recommend keeping a combination of on-box backups along with copying said backups off-box as well. Obviously, keeping databases AND backups on the SAME server is the metaphorical equivalent of putting all of your eggs in one basket – and therefore something you should avoid like the plague....More

One of the biggest strengths of AlwaysOn Availability Groups is that they allow DBAs to address both high availability and disaster recovery concerns from a single set of tooling or interfaces. But, this doesn’t mean that you won’t still need backups....More