Microsoft SQL Server 2008 R2 SP1 Release Notes

Microsoft SQL Server 2008 R2 SP1 Release Notes

Click here to change the language.

This page is carefully and closely monitored. Any changes you make will be evaluated and then quickly accepted, refined, or reverted. Because this is a wiki, additions or refinements to these release notes might have been made by community members. To read the original and official release notes document, click here.

This Release Notes document describes known issues that you should read about before you install or troubleshoot SQL Server 2008 R2 SP1. You can download SQL Server 2008 R2 Service Pack 1 (
click here to download it).  You can download SQL Server 2008 R2 SP1 Express Edition at: http://www.microsoft.com/download/en/details.aspx?id=26729.

This document also provides search tips to help you find more known issues in Knowledge Base (KB) articles on the
Microsoft Support site for SQL Server 2008 R2. This Release Notes document and the KB articles supplement SQL Server 2008 R2 Books Online.

This Release Notes document is available only online, not on the installation media, and it is updated periodically.

For information about how to get started with installing SQL Server 2008 R2, see the SQL Server 2008 R2 Readme. The Readme is available on the installation media and from the Readme page.

If you have feedback about SQL Server 2008 R2, please submit it at Microsoft SQL Server Connect Feedback Center. For the latest documentation, view SQL Server 2008 R2 Books Online in the MSDN Library. You can also find our forums and other support on the Troubleshooting and Support page.

Contents


1.0 What's New in Service Pack 1

The following features are new in Service Pack 1 (SP1).

  • Dynamic Management Views for increased supportability. sys.dm_query_stats DMV is extended with additional columns to improve supportabilities over troubleshooting long-running queries. New DMVs and XEvents on select performance counters are introduced to monitor OS configurations and resource conditions related to the SQL Server instance.
  • ForceSeek for improved querying performance. Syntax for FORCESEEK index hint has been modified to take optional parameters allowing it to control the access method on the index even further. Using old style syntax for FORCESEEK remains unmodified and works as before. In addition to that, a new query hint, FORCESCAN has been added. It complements the FORCESEEK hint allowing specifying ‘scan’ as the access method to the index. No changes to applications are necessary if you do not plan to use this new functionality.  
     
  • Data-tier Application Component Framework (DAC Fx) for improved database upgrades. The new Data-tier Application (DAC) Framework v1.1 and DAC upgrade wizard enable the new in-place upgrade service for database schema management. The new in-place upgrade service will upgrade the schema for an existing database in SQL Azure and the versions of SQL Server supported by DAC. A DAC is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.

  • Disk space control in PowerPivot for SharePoint. This update introduces two new configuration settings that let you determine how long cached data stays in the system. In the new Disk Cache section on the PowerPivot configuration page, you can specify how long an inactive database remains in memory before it is unloaded. You can also limit how long a cached file is kept on disk before it is deleted.   
     
  • Support for 512e Drives. SQL Server now correctly detects and supports hard drives with the new 512e format. These drives report 512 byte logical sector sizes, but they are formatted internally using 4KB sectors. When SQL Server 2008 R2 SP1 is installed on Windows Server 2008 R2 or higher, we will correctly detect these drives and adjust automatically.
  • Fixed various issues. This update fixes many SQL Server 2008 R2 issues that are listed in this master KB article. It also includes some additional fixes that improve the product quality. 


2.0 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.

2.1 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.

2.2 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

Symptoms

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.

Cause

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.

Resolution

If you experience this behavior 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;
RECONFIGURE;
EXEC sp_configure 'allow updates', 0;
RECONFIGURE;
EXEC sp_configure 'Agent XPs', 1;
RECONFIGURE;
GO

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.


3.0 How to Find More Known Issues

We recommend that you view more known issues on the Microsoft Support site for SQL Server 2008 R2 or by searching for "SQL Server 2008 R2" on the main Microsoft Support Web site.

You can also join the SQL Server Community to share your experiences and learn from other people who are using SQL Server 2008. For more information, see the SQL Server Community Web site. You can also find our forums and other support on the Troubleshooting and Support page.



Books Online is the primary documentation for SQL Server 2008. You can view SQL Server 2008 R2 Books Online in the MSDN Library.

Sort by: Published Date | Most Recent | Most Useful
Comments
  • It would really be nice if you folks actuall put some steps in the readme file that staes what you are suppose to do other then just:

    Note: You must have administrative rights on the computer to install SQL Server 2008 R2 SP1.

    Step 1: Download and review the Setup documentation and readme file.

    For known issues, review the Release notes.

    Step 2: Download the appropriate package by clicking one of the links below.

    ◦To start the installation immediately, click Run.

    ◦To install SP1 at a later time, click Save.

    what about what services need to be runnjing and what services do not need to be running ?

    what about info on backing up any files before the install ?

    stuff like that.

    al you say is have admin rights and click run

    Really? that simple ? Right !

    Thanks

    JC

  • @JC3113,

    Have you noticed any services that should not be running? Have you noticed any need to back up any files?

    You have great points, but we don't want to add information unless it's truly mitigating a risk.

    Thank you for the input!

    Ed Price, Microsoft SQL Server

  • According to the download page (www.microsoft.com/.../details.aspx) SP1 contains fixes from cumulative update 1 through 6.

    How do we then handle upgrades from SQL Server 2008 R2 CU7 or CU8?

    Thanks, Mikael.

  • Does SQL 2008 R2 SP1 Include MS11-049 ? If not, is there any new Security bulletin coming up for it?

  • Instructions for installing on a SQL Cluster (active-passive) should be provided.

  • INSTALLING ON A SQL CLUSTER

    A KB article exists "SQL Server 2008 failover cluster rolling patch and service pack process" support.microsoft.com/.../958734  

    Article applies to

    •Microsoft SQL Server 2008 Enterprise

    •Microsoft SQL Server 2008 Standard

    •Microsoft SQL Server 2008 Enterprise Evaluation

    •Microsoft SQL Server 2008 R2 Enterprise

    •Microsoft SQL Server 2008 R2 Standard

  • I'm confused about upgrading SQL Server 2008 R2 Express w/Advanced Services (10.50.1600) to the same version, with SP1.

    On the SQL Server 2008 R2 SP1 page ( www.microsoft.com/.../details.aspx ) it tells me:

    "To upgrade SQL Server 2008 R2 Express Edition, obtain the SP1 version of Express Edition or Express Edition with Advanced Services."

    It doesn't specify an exact URL, but I found this page, which seemed the right place:

    www.microsoft.com/.../details.aspx

    I assumed that I needed to download SQLEXPRADV_x64_ENU.exe, but using this I can't seem to upgrade to SP1 - I only have options for a new installation, or an upgrade - both of which return validation errors (correctly).

    Can anyone point me in the right direction?

    Many thanks,

    Simon

  • Is the undocumented stored procedure sp_MSforeachtable still available? Any info on this?

    Thanks

    Aneesh

  • Check your SQL Server Default data and log directory settings before patching to avoid much grief.

    If these directories are not valid the upgrade will tell you it worked but SQL Server will not start properly.  It will look like the master database has an issue.  Luckily my issue was in test and I thank this poster for an easy solution.

    - blogs.msdn.com/.../sql-server-2008-service-fails-to-start-after-applying-service-pack-1.aspx

  • It seems that installing the full-build SQL Server 2008 R2 SP1 Express does not upgrade an existing 2008 R2 install. Is that correct? Is there a command-line or other method to cause an upgrade when an existing install is found? I'm bundling SQL Server Express with my application install. If someone upgrades my application, their setup will fail on SQL Server Express R2 (SP1) install because of this limitation.

    Thanks,

    Paul T.

Page 1 of 2 (11 items) 12