Microsoft SQL Server 2008 R2 SP1 Release Notes

Article
1/17/2024

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.

1.0 What’s New in Service Pack 1

2.0 Before You Install

3.0 How to Find More Known Issues

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.

If SQL Server is running, stop the SQL Server service.
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.

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.
Open SQL Server Management Studio, and then connect to the instance of SQL Server 2008 R2.
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

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.

In SQL Server Configuration Manager, right-click the SQL Server service, and then click Stop.
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.
Right-click the SQL Server service, and then click Start.
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.
In SQL Server Management Studio, reconnect to the instance of SQL Server 2008 R2.
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.

Share via