SQL Server 2016 Service Pack 2 release information

Applies to: SQL Server 2016 DeveloperSQL Server 2016 EnterpriseSQL Server 2016 Enterprise Core More

This article contains important information to read before you install Microsoft SQL Server 2016 Service Pack 2 (SP2). It describes how to get the service pack, the list of fixes that are included in the service pack, known issues, and a list of copyright attributions for the product.

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.

More Information


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 Download the package now.
SQL Server 2016 SP2 Express Download Download the package now.
Microsoft SQL Server 2016 SP2 Feature Pack Download 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 SP2

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

Notes
 
  • 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.

For more information about the bugs that are fixed in SQL Server 2016 SP2, go to the following Microsoft Knowledge Base articles.

VSTS bug number KB article number Description
11032173 4048942 FIX: Memory dumps generated for "Stalled IOCP Listener" and "non-yielding IOCP listener" after SQL Server restart
3507192 4051361 Optimizer row goal information in query execution plan added in SQL Server 2017 CU3 and SQL Server 2016 SP2
9912272 4051360 FIX: Execution stats of scalar user-defined function is added to Showplan XML file in SQL Server 2016 SP2
10937127 4051359 FIX: SQL Server runs out of memory when table-valued parameters are captured in Extended Events sessions in SQL Server 2016 even if collecting statement or data stream isn't enabled
5660983 4051358 Update adds new columns to SYS.DM_EXEC_QUERY_STATS DMV in SQL Server 2016 SP2
10077001 4052138 FIX: Parallel queries are slower when they are run with high DOP in SQL Server 2016
10732726 4052137 Improve query performance on a partitioned table in SQL Server 2016 SP2
11086264 4052136 FIX: SQL Server Audit Events fail to write to the security log
10696815 4038419 Update adds CPU timeout setting to Resource Governor workgroup REQUEST_MAX_CPU_TIME_SEC in SQL Server 2016 and 2017
9443219 4038418 Update to add a new column to DMV sys.dm_sql_referenced_entities in SQL Server 2016 SP2
8025121 3157575 FIX: Copy Database Wizard fails when a table contains a sparse column set in SQL Server
7990665 3162858 "The log backup chain is broken" error when the log backup process fails in SQL Server
10727149 3170116 FIX: Memory grant that's required to run optimized nested loop join isn't reflected in Showplan XML in SQL Server
10871961 4013128 FIX: Decreased performance and long waits for CLR_AUTO_EVENT and CMEMTHREAD when SQLCLR UDT is used as a stored procedure parameter for a SQL RPC call
10697582 4014756 FIX: SQL Server Profiler fails to obfuscate sp_setapprole when it's executed from a remote procedure call in SQL Server
10871974 4016949 Unified Showplan Schema for SQL Server starting in SQL Server 2012 SP4 and SQL Server 2016 SP2
10973046 4046918 FIX: Execution fails when a SQL CLR function invokes Transact-SQL statements through impersonation calls in SQL Server 2016 and 2017

11032146

4045795 FIX: Thread pool exhaustion and CMEMTHREAD contention in AAG with data seeding in SQL Server 2016 and 2017
10872004 4056008 FIX: "sp_special_columns" returns incorrect result in SQL Server 2016
10726760 4052129 Update for manual change tracking cleanup procedure in SQL Server 2016 and 2017
10065114 3136496 FIX: Access violation occurs when a query references an SQLCLR function through a synonym in SQL Server 2016
9832539 4039846 FIX: TRY…CATCH block rolls back too many transactions in some in-memory OLTP error handling scenarios in SQL Server 2016
10727775 4041809 Update adds support for MAXDOP option for CREATE STATISTICS and UPDATE STATISTICS statements in SQL Server 2016 and 2017
6884989 4041811 FIX: Automatic update of incremental statistics is delayed in SQL Server 2016 and 2017

10385772

4041814 Improve tempdb spill diagnostics in DMV and Extended Events in SQL Server 2017 and SQL Server 2016 SP2

9653457

4041817 Update enables XML Showplans to provide a list of statistics used during query optimization in SQL Server 2016 and 2017

10817173

4040276 FIX: Indirect checkpoints on tempdb database cause "Non-yielding scheduler" error in SQL Server 2016 or 2017
 10914360 4042415 FIX: Deadlock occurs on single-user mode database when many connections access it simultaneously in SQL Server 2016
 10704351 4042788 FIX: The tempdb system database (sys.databases) is still encrypted even though all other databases on the instance of SQL Server are not encrypted
10456101 4046914 FIX: Add CXPACKET wait type in showplan XML in SQL Server 2016 and 2017
8846046 4046909 FIX: LogPool cache MEMORYCLERK_SQLLOGPOOL consumes more memory when you do online transactions in SQL Server 2016 SP1 Express Edition
 10761398 4046902 FIX: "TempDB file size exceeds 1024 MB" error when you try to set the initial size for a TempDB file to a value greater than 1024 MB
10571989 4046885 FIX: The SQL Server 2016 setup page contains nonsecure tool-download links
9956170 4018930 Update adds the "CLR strict security" feature to SQL Server 2016
11334467 4075158 FIX: Boolean values not localized correctly in the parameters pane of a report in SSRS 2016 (SharePoint mode)
11542281 4057054 FIX: CXPACKET and CXCONSUMER wait types show inconsistent results for some parallel query plans in SQL Server 2016 and 2017
10932023 4043624 FIX: Change tracking manual cleanup fails with table non-existence error in SQL Server
10871968 4021243 FIX: Access violation when you create or configure an Always On availability group for an availability database in SQL Server 2012 and 2016
11454650 4089239 FIX: QRY_PROFILE_LIST_MUTEX is blocked when TF 7412 is enabled in SQL Server 2016
11566335 4089509 FIX: Access Violation when you use QDS and specify a query plan to resolve a performance regression
11523867 4090279 FIX: It takes a long time to roll back a batch that updates a large amount of data in SQL Server 2016
9191595 4092045 FIX: LSN truncation occurs in AG when disabling the change data capture feature in SQL Server 2016
10288012 4092046 FIX: Cannot restart SQL Server 2016 after you use ALTER DATABASE ADD FILE or ADD LOG commands to add files with the same logical name
11543252 4092066 FIX: Replication isn't enabled when database collation uses '_SC' collation extension in SQL Server 2016 and 2017
9824203 4092069 Improves the Distribution Agent cleanup procedure to increase the transactional replication performance in SQL server 2016
10895916 4092075 Improvement: Adds Service Broker support for DBCC CLONEDATABASE in SQL Server 2016
11555288 4089473 Better intra-query parallelism deadlocks troubleshooting in SQL Server 2017 and 2016
11578523 4089718 Improvement: Performance issue when upgrading MDS from SQL Server 2012 to 2016
10871976 4016361 FIX: Service Broker endpoint connections aren't closed after an availability group failover in SQL Server
8742052 4054842 Improvement: General improvements to the change tracking cleanup process in SQL Server 2016 and 2017
12519464 4475794 FIX: Access violation exception occurs during Query Optimization of a query that accesses a table with Filtered Indexes or Statistics

Notes for this update


Additional resolutions

Resolutions to the following issues are also included in SQL Server 2016 SP2.
 
VSTS bug number Description Area

10698782

Adds a new DMV sys.dm_db_log_stats that returns summary level attributes and information about transaction log files of databases. SQL Engine

10698846

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

10723493

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 Error: 41381, Severity: 21, State: 1.
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.
 

References


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.