…. And it’s finally available for Download. Actually it’s been pretty exciting couple of weeks , SharePoint 2010 Service Pack1 and now SQL 2008 R2 SP1 .

Download Links for Service Pack for SQL are available here http://www.microsoft.com/download/en/details.aspx?id=26727&utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+MicrosoftDownloadCenter+%28Microsoft+Download+Center%29

The KB article might be your preferred first place of hunting for Bugs or issues you might have been facing and to check if they have been rectified or addressed http://support.microsoft.com/kb/2463333

So our playground for this post is

  1. Windows 2008 R2 Sp1
  2. SQL 2008 R2


Before You Install Service Pack 1

For a list of the bugs that are fixed in SQL Server 2008 R2 Service Pack 1, see the master KB article.

Recommended Disk Space Requirements to Download, Extract, and Install SQL Server 2008 R2 Service Pack 1

The recommended disk space requirements to download, extract, and install SP1 are approximately 2.5 times the size of the specific package.

The SQL Server Service Cannot Start after You Uninstall SP1 for SQL Server 2008 R2 if a UCP Exists in the Instance of SQL Server


Consider the following scenario:

  • You have an instance of Microsoft SQL Server 2008 R2 that hosts a utility control point (UCP).
  • You have installed SP1 for SQL Server 2008 R2 on the instance and later decide to uninstall it.

In this scenario, the uninstall action succeeds. However, the SQL Server service cannot start.


This problem occurs because the Agent XPs option is not enabled when SQL Server 2008 R2 runs the upgrade script for the msdb database.

The upgrade script runs when the SQL Server service starts. The script runs a stored procedure that is related to the UCP. The stored procedure requires that the Agent XPs option be enabled.


If you experience this behaviour after removing SP1, follow these steps to work around the problem. To avoid the problem, perform these steps before removing SP1.

1) If SQL Server is running, stop the SQL Server service.

2) Enable trace flag 902 on the instance of SQL Server 2008 R2. To do this, follow these steps:

  • Open SQL Server Configuration Manager.
  • In SQL Server Configuration Manager, click SQL Server Services.
  • Double-click the SQL Server service.
  • In the SQL Server Properties dialog box, click the advanced tab.
  • On the Advanced tab, locate the Startup Parameters item.
  • Add ;–T902 to the end of the existing string value, and then click OK.

3) Right-click the SQL Server service, and then click Start.

4) If the SQL Server Agent service is running, right-click the SQL Server Agent service, and then click Stop. Set the Agent service start mode to Manual.

5) Open SQL Server Management Studio, and then connect to the instance of SQL Server 2008 R2.

6) Run the following statements:

EXEC sp_configure ‘show advanced’, 1;
EXEC sp_configure ‘allow updates’, 0;
EXEC sp_configure ‘Agent XPs’, 1;

7) Modify the extended property of the sysutility_mdw database back to the SQL Server 2008 R2 version. To do this, follow these steps:

  • In SQL Server Management Studio, right-click the sysutility_mdw database and then click Properties.
  • In the property dialog box, click Extended Properties.
  • Update the value to 10.50.1600.1 for the “Microsoft_DataCollector_MDW_Version” property.

8) If you have not yet uninstalled the service pack, uninstall it now. If you have already uninstalled it, continue to step 9.

9) In SQL Server Configuration Manager, right-click the SQL Server service, and then click Stop.

10) Remove trace flag 902 on the instance of SQL Server 2008 R2. To do this, delete ;–T902 from the string value that you updated in step 2.

11) Right-click the SQL Server service, and then click Start.

12) Right-click the SQL Server Agent service, view properties, and set the service start mode back to its original value. Then click Start to start the Agent service.

13) In SQL Server Management Studio, reconnect to the instance of SQL Server 2008 R2.

14) In Object Explorer, expand Management, right-click Data Collection, and then click Enable Data Collection.

Note: If data collection is already enabled, the Enable Data Collection item is unavailable.


Download Size


Installing SP1

Updating Management Studio

And then continue the installation to update your SQL Management Studio. If you have applied SP1 earlier you wouldn’t need to run the Update SQL Management Studio after that.

SQL 2008 R2 SP1.

As always do this several times within a TEST ENVIRONMENT before trying it anywhere closer than 2km’s from your Production Datacentre J