Note This article serves as a single source of information to locate all documentation that's related to this service pack. It includes all the information that you previously found in the release notes and Readme.txt files.
How to get SQL Server 2016 SP2
SQL Server 2016 SP2, Microsoft SQL Server 2016 SP2 Express, and Microsoft SQL Server 2016 SP2 Feature Pack are available for manual download and installation at the following Microsoft Download Center websites.
|SQL Server 2016 SP2||Download the package now.|
|SQL Server 2016 SP2 Express||Download the package now.|
|Microsoft SQL Server 2016 SP2 Feature Pack||Download the package now.|
Note After you install the service pack, the SQL Server service version should be 13.0.5026.0 Microsoft SQL Server 2016 service packs are cumulative updates. SQL Server 2016 SP2 upgrades all editions and service levels of SQL Server 2016 to SQL Server 2016 SP2.
List of fixes included in SQL Server 2016 SP2Microsoft SQL Server 2016 service packs are cumulative updates. SQL Server 2016 SP2 upgrades all editions and service levels of SQL Server 2016 to SQL Server 2016 SP2. In addition to the fixes that are listed in this article, SQL Server 2016 SP2 includes hotfixes that were included in SQL Server 2016 Cumulative Update 1 (CU1) to SQL Server 2016 SP1 CU8.
For more information about the cumulative updates that are available in SQL Server 2016, see SQL Server 2016 build versions.
- Additional fixes that aren't documented here may also be included in the service pack.
- This list will be updated when more articles are released.
Notes for this update
Additional resolutionsResolutions to the following issues are also included in SQL Server 2016 SP2.
|VSTS bug number||Description||Area|
|Adds a new DMV sys.dm_db_log_stats that returns summary level attributes and information about transaction log files of databases.||SQL Engine|
|Adds a new DMV sys.dm_tran_version_store_space_usage that returns a table to display total space in tempdb used by version store records for each database.||SQL Engine|
|10760480||Exposes the processor information such as core count, sockets, and NUMA information in DMV sys.dm_server_services and the SERVERPROPERTY function.||SQL Engine|
|10755072||Adds the filegroup support to the SELECT INTO statement. This allows you to specify the name of the filegroup in which a new table will be created. The filegroup specified should exist on the database else the SQL Server engine throws an error.||SQL Engine|
|9742982||Fixes an issue in which the plan cache memory usage is high when the MAXDOP is set to 1.||SQL Engine|
|10698847||Fixes an issue in which restore of a compressed backup for a Transparent Data Encryption (TDE) enabled database through the Virtual Device Interface (VDI) interface may fail with the operating system error 38.||SQL Engine|
|10698823||Exposes the percentage of differential changes in the databases to help determine whether full database backup or differential backup is useful.||SQL Engine|
|10823015||Improves the backup performance on computers that have large memory.||SQL Engine|
|10697461||Fixes an issue in which the MAX_GRANT_PERCENT query hint does not always being respected.||SQL Performance|
|10756412||Adds the WITH VERIFY_CLONE option and the WITH BACKUP_CLONE option to the DBCC CLONEDATABASE management command that allows you to verify and back up cloned databases.||SQL Engine|
|10925573||Adds a new extended event marked_transaction_latch_trace that shows the state of the global latches that are used by marked transactions.||SQL Engine|
|8837822||Fixes an issue in which the DMV sys.dm_exec_query_profiles shows an incorrect result that's inconsistent with the estimated or actual query plan.||SQL Performance|
|10698786||Adds a new DMV sys.dm_db_log_info that returns the Virtual Log File (VLF) information about the transaction log files.||SQL Engine|
|10914366||Adds a database name attribute in the process-list and executionStack in the extended event xml_deadlock_report to expose the database name.||SQL Engine|
|Fixes an issue in which an assertion error might occur when you query the DMV sys.dm_db_stats_histogram if the TIMESTAMP column contains values.||SQL Performance|
|9437530||Fixes the memory grant usage (InputMemoryGrant, OutputMemoryGrant and UsedMemoryGrant properties) in Showplan XML for parallel queries.||SQL Performance|
|9480942||Fixes an issue to avoid null reference in GetNextAllHoBts.||SQL Engine|
|8523231||Adds durable memory optimized tables that can have foreign key reference to nondurable memory optimized tables.||In-Memory OLTP|
|9737945||Adds a DVM sys.dm_tran_version_store_space_usage that can track the tempdb version store usage in each database.||SQL Engine|
|9146914||Adds the SpLevel and ReleaseProductVersion properties to SQL Server 2016 SP1.||Setup & Install|
|10365365||Fixes an issue by installing a SQL Server security update on the passive node in a customized cluster.||Setup & Install|
|11565681||Exposes the last-known good DBCC CHECKDB date and time for a database.||SQL Engine|
|11583626||Fixes an issue by removing a node from a failover cluster that has Polybase installed.||SQL Engine|
|11559743||Fixes an issue in which you are unable to run the ADDNODE step when the Polybase feature is installed in ScaleOut deployment mode.||SQL Engine|
|10973764||Improves the Distributed Transaction Coordinators (DTC) service support for AlwaysOn availability groups (AG) databases.||SQL Engine|
For more information about how to upgrade your SQL Server installation to SQL Server 2016 SP2, see Supported Version and Edition Upgrades.
Uninstalling SQL Server 2016 SP2 (not recommended)
If, for any reason, you choose to uninstall SQL Server 2016 SP2, the uninstallation of SQL Server 2016 SP2 will not be blocked, and you will be able to uninstall SQL Server 2016 SP2 in the same manner as any other service packs. However, if you are running the Standard, Web, or Express edition of SQL Server, and you are using some new features that are unlocked only when you start SQL Server 2016 SP2, you might experience errors or see databases left in a suspect state after the uninstallation of SQL Server 2016 SP2. Even worse, if the system databases are using new features (for example, partitioned tables in master databases), this could prevent SQL Server instances from starting after you uninstall SQL Server 2016 SP2.
We recommend that you verify that all the new features are disabled or dropped before you choose to uninstall SQL Server 2016 SP2 on editions other than the Enterprise edition. You cannot drop the memory_optimized_data filegroup. Therefore, if you have set up the memory_optimized_data filegroup on your database with SP2, you should not uninstall SQL Server 2016 SP2. Otherwise, the database will get into a suspect state, and the following entry will be logged in the error log:
DateTime spid15s The database cannot be started in this edition of SQL Server because it contains a MEMORY_OPTIMIZED_DATA filegroup. See Books Online for more details on feature support in different SQL Server editions.
For more information about how to determine the current SQL Server version and edition, select the following article number to go to the article in the Microsoft Knowledge Base:
321185 How to identify your SQL Server version and edition
The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.