SQL Server 2005 Service Pack 2

By:   |   Updated: 2007-01-22   |   Comments (2)   |   Related: More > Upgrades and Migrations

As with past versions of SQL Server, Microsoft is releasing service pack updates for SQL Server 2005.  The product officially launched in November 2005 and there has already been one service pack release on March 18, 2006 and service pack 2 is in the works.  Microsoft is making these pre-releases available with their CTP version or Community Technology Preview.  The problem comes into play as to who should test these versions and how should they be tested?

At the current time there looks like there are about 75 fixes in the service pack.  You can see a list of these fixes here.  The following table is a breakdown of the fixes.  Although it is not perfect, it does give you an idea of what types of fixes are included in this next service pack release.  As you can see from this listing the two big areas affect Analysis Services and T-SQL with the rest of the fixes scattered throughout several other categories.

Administration 1
Analysis Services 22
Backup 3
Configuration 1
Database Mail 1
Misc 2
Notification Services  1
Performance 3
Profiler 1
Query Optimizer 2
Replication 3
Reporting Services  4
Security 2
SQL Mobile 1
Trace Flags 1
T-SQL 21


Who should test?
The nice thing about this process that Microsoft has put in place, is that they allow everyone the opportunity of checking out the new service pack release prior to its official release.  This gives more people the opportunity of reporting issues and bugs back to Microsoft prior to the release.  The downside to this is that it takes time to test your applications or to test a known problem that is slated to be fixed in the next service pack.   Usually you wait for someone else to find the problem and report it.  By making the CTP version available now, the hope is that the testing and bug fix cycle is much quicker and more thorough

How to test?
The ideal way would be to test your entire application and run your database through a series of test.  The reality is that most people don't have time to do this unless this is their full time job.  The next best thing would be to take a look at the areas that have been a problem and to see if any of these fixes address your issue at hand. It would probably be more beneficial to both you and the SQL Server user community to thoroughly test one aspect versus trying to hit each one of the bugs.

Next Steps
Be prepared for the next service pack release and take the time to see if any of these issues that have been addressed will be beneficial to your database servers. 

Here are a couple of links to get the latest service pack as well as service pack 1 for SQL Server 2005.

Last Updated: 2007-01-22

get scripts

next tip button

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources

Comments For This Article

Wednesday, January 20, 2010 - 7:49:51 AM - admin Back To Top

When you install service packs you need to update each installed component such as the database engine, reporting services, ssis etc...

Also, you should apply the service pack to your workstation as well to update SQL Server Management Studio.

When installing the service pack there will be some downtime.  This is primarily to stop and restart the services, so the downtime is quick.


Wednesday, January 20, 2010 - 5:57:49 AM - yakaaithiri Back To Top

hi Admin ,

what r all the steps to be followed in applying a service pack.?

 There is a problem with workstation components in my environment after applying SP2 .

So i am planing to reinstall SP2.

Will Installing SP2 require any Downtime ?

Whether the services need to be restarted after running SP2.?


Balabharathi Narayanan


Recommended Reading

Downgrade from SQL Server Enterprise Edition to Standard Edition

Migrate a database from SQL Server 2008 to 2016 and use new features

SQL Server Database Migration Checklist

SQL Server Upgrade Methods In-Place Upgrades and Differential Restore Upgrades - Part 2

Choosing a SQL Server Upgrade Method - Part 1

get free sql tips
agree to terms

Learn more about SQL Server tools