This article lists the bugs that are fixed in Microsoft SQL Server 2012 Service Pack 2 (SP2). Notes
Additional fixes that are not documented here may also be included in the service pack.
This list will be updated when more articles are released.
For more information about how to obtain SQL Server 2012 service packs, click the following article number to view the article in the Microsoft Knowledge Base:
2755533 How to obtain the latest service pack for SQL Server 2012
Note For more information about which download you should select from the available downloads list, check the release notes here.
SQL Server 2012 service packs are cumulative updates and upgrade all editions and service levels of SQL Server 2012 to SP2. In addition to the fixes that are listed in this article, this service pack contains up to and including Microsoft SQL Server 2012 SP1 Cumulative Update 9 (CU9).
For more information about the cumulative update packages that are available for SQL Server 2012 SP1, click the following article number to view the article in the Microsoft Knowledge Base:
2772858 The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released
Issues that are fixed in this service pack
For more information about the bugs that are fixed in SQL Server 2012 SP2, click the following article numbers to view the articles in the Microsoft Knowledge Base.
FIX: Incorrect results when you run queries that contain UNION operator by using parallel query plan in SQL Server
Resolutions to the following issues are also included in SQL Server 2012 SP2.
VSTS bug number
Improved messaging around processing errors and warnings when you work with large HTML reports.
Improved messaging around processing errors and warnings when you work with reports with large number of parameters.
Incorrect results when you work with block mode unary operators.
DPM’s full backup on the server with AlwaysOn secondary will be converted to copy_only backup.
The value of tabular instance perfmon counters: Memory\VertiPaq <TypeName> KB is always 0.
The end SCOPE statement does not match the opening SCOPE statement.
FIX: Shared components are installed in the wrong location when you implement a SQL Server 2008 Failover Cluster installation
Uninitialized object leads to crashes and potentially wrong results when executing DAX expressions with conditional calculations.
A simple DAX query returns unexpected results when you use PowerPivot v2 11.0.3000.0.
"ACTIONABLE_HEAP_CORRUPTION" when SSRS configuration file is parsed by using an invalid InstanceId.
NonYieldProcessTable record in RING_BUFFER_NONYIELD_PROCESSTABLE ring buffer does not have user and kernel process time.
Provides platform information in the log of SSRS 2012
FIX: Access Violation when you insert data into a table that has a column with a sequence default from a cross-database transaction in SQL Server 2012
If a tail log backup is performed against the model database, model database will freeze in restoring state during a server restart that will prevent the SQL Server from starting successfully. This is because Tempdb database could not be created during the server startup and you receive the following error message: “Database '<DatabaseName>' cannot be opened"
Error 3456 occurs when restoring the log backup of a bulk-logged DB where a new data file was added with an initial size greater than 64704KB.
When you drop of a database snapshot is issued in a session with a LOCK_TIMEOUT setting, a time-out error may occur followed by an assertion error.
Installation of SQL Server by using InstallShield ends up in a hang state. For more information, go to this Knowledge Base article.
After you install SQL Server 2012 SP2, when SQL Server is stopped from either configuration manager or service control manager, an informational message is logged to the SQL Server immediately after shutdown is issued. In earlier versions, we issued this messages after internal services, such as the .NET Framework, service broker, were stopped.
Fixes for Connect Items submitted by the community
You cannot debug a stored procedure that calls sp_executesql in SQL Server Management Studio (SSMS). When F11 is pressed, you receive an 'Object reference not set to an instance of object' error message.
After reverting to a database snapshot that contains full-text indexes, you cannot create any ft_catalogs until you either restart SQL Server, detach and then attach the database, or take the database offline and then set the database online.
When you execute queries with Show Actual Query Plan turned on, the result of Null is returned, and you receive the following error message: Error: Msg 50000, Level 16, State 10, Procedure test, Line 34 String or binary data would be truncated.
Deploying new versions of large projects encounters a time-out during deployment into SSIS Catalog database (SSISDB). Additionally, you receive the following error messages: Failed to deploy project. For more information, query the operation_messages view for the operation identifier ’219′. (Microsoft SQL Server, Error: 27203) Failed to deploy the project. Fix the problems and try again later.:Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.
"Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries" error message when you parse or execute stored procedure.
Highlights for SQL Server 2012 SP2
Here is a highlight of some of the changes that are in SQL Server 2012 Service Pack 2 (aka SP2):
Message 35285 is displayed as an informational message as expected and not as an error.
SQL Writer support COPY_ONLY backup: Data Protection Manager (DPM) full backup on the server with AlwaysOn secondary will be converted to COPY_ONLY backup.
Analysis Services (aka SSAS)
Add support to local cube creation scenario:
When you use SQL Server 2012 Analysis Services (SSAS 2012) SP2, you can create a local cube that connects to SSAS that is running on a static non-default port that has SQL browser turned off. This new feature is controlled by a new configuration setting in msmdsrv.ini: "ConfigurationSettings\LocalCubeServerPortInConnectionString." To enable this feature, set the value to 1. This setting will have no effect if SSAS 2012 is configured as a failover cluster or is running on the default port.
Add more logging:
This service pack adds more information to SSAS log file to help improve troubleshooting. This includes SSAS version, computer name, netbios name, number of CPUs, RAM size, IP, port, protocol type, deadlock, lock timeout, and some other information.
Integration Services (aka SSIS)
SSISDB deadlock and cleanup performance improvement
Deadlock in SSISDB database on concurrent calls to SSISDB.catalog.create_execution stored procedure
A fix has been done for SSISDB concurrences and deadlock issue. However, this does not resolve all problems. Current workaround is staggering the jobs by a few seconds. But you may not use this workaround.
Poor performance on SSISDB maintenance job
A hotfix is available for SSISDB maintenance job.
Each transaction creates a certificate and symmetric key, which must also be stored and processed in maintenance or cleanup tasks.
Introduce new row in catalog property table SERVER_OPERATION_ENCRYPTION_LEVEL and default to PER_EXECUTION to keep backward compatibility, value can be changed to PER_PROJECT creating one key or certificate pair for each project. A full cleanup is required before changing from PER_EXECUTION to PER_PROJECT. Two new store procedures are introduced for a full cleanup.
Introduce a new row in SSISDB property table SERVER_OPERATION_ENCRYPTION_LEVEL:
Only two values are valid (1 and 2).
Value "1": Default value. Encrypted with symmetric keys and certificates according to execution level. High security, but it may has performance or deadlock issue.
Value "2": Encrypted with symmetric keys and certificates according to project level. Less security, resolve performance or deadlock issue.
Introduce two new stored procedures (SPs) Cleanup_server_logs and cleanup_server_execution_keys
SPs are designed to be used when you change encryption level from "1" to "2." You can run Cleanup_server_logs first that can be finished very quickly to bring SSISDB into a good state. The SSISDB database is ready to be changed to encryption level.
Cleanup_server_keys takes longer time than Cleanup_server_logs. However, it is run after encryption level is changed to "2" and run periodically at off peak time.
Cleanup_server_logs: Only clean up all operation logs.
Cleanup_server_execution_keys: clean up symmetric keys and certificates that are used to protect sensitive execution parameters.
@cleanup_flag int (in (1,2))
cleanup_flag = 1: drop execution level symmetric keys and certificates
cleanup_flag = 2: drop project level symmetric keys and certificates (will only drop them if this perticular project has been deleted and all operations logs related to this project has been cleaned).
@delete_batch_size int = 1000
number of keys or certificates will be dropped at one call of this store procedure.
In internal.cleanup_server_retention_window, change delete_batch_size from 10 to 1000.
Optional support for table variable row counts to improve query plans:
If a table variable is joined with other tables in SQL Server, it may result in slow performance due to inefficient query plan selection because SQL Server does not support statistics or track number of rows in a table variable while compiling a query plan.
In SQL Server 2012 SP2, a new trace flag is introduced that allows the query optimizer to use information about the number of rows inserted into a table variable in order to select more efficient query plan. Enable trace flag 2453 to activate this behavior.
In some scenarios, enabling trace flag 2453 may result in some degradation of performance, due to additional compilation required to account for actual number of rows inserted into a table variable during execution time. Typically, you would benefit from this trace flag if a table variable has significant amount of rows joined with other tables, or has more than one row and used at the outer side of a nested loop join operator with a plan on the inner side that processes large amount of rows.
Similar behavior may be achieved on other versions of SQL Server through using OPTION (RECOMPILE) query hint. However, the query hint requires detecting and modifying all queries suffering from poor plan choice due to large amount of work driven by table variables, while enabling the trace flag 2453 can impact existing workloads.
Improved performance troubleshooting for parallel query execution plans
DMV sys.dm_exec_requests now returns accurate value for cpu_time for parallel query execution plans. STATISTICS IO diagnostics now reports accurate logical reads for parallel query execution plans.
Improved hash spill diagnostics
If a hash join or hash aggregate operator spills data to tempdb database during query execution, corresponding I/O is now reported for STATISTICS IO diagnostics.
Improved full text index diagnostics
In some cases it may be difficult to understand how results of certain full text queries are returned. The full text search subsystem interacts with external word-breakers and stop-lists, and as a result of this interaction the positional information for the keywords in a document may be different from what is observed in the actual text. This is highly relevant when doing 'phrase' or 'NEAR' searches since the delta between the words is used in internal calculations to find matching documents.
In SQL Server 2012 SP2 a new Dynamic Management Function (DMF) was added to provide access to positioning information for keywords indexed in a document. The new DMF is similar to existing DMF sys.dm_fts_index_keywords_by_document, and has the following syntax:
Instead of occurrence count, it returns information about each occurrence and its position in the indexed document.
This information can be used to look at the mapping between the text and the internal index which will show the difference in delta, if any, for the search phrase. This can help understanding results from phrase search.
Verbose message added to the stats in history tables and more detailed messages in the agent logs:
Added text message to <stats> xml output to define different states in both logreader and distributor agents.
Added article level stats for distributor and logreader parameterized commands.
Added number of skipped commands to output file in distributor agent.
Added time spent doing schema changes in distributor agent.
LogReader: log records are added in .TXT file when a user dump is captured.
Peer To Peer: Insert, update and delete procedures are updated to provide below details to the conflict message:
Primary Key column name and values
The output of trace flag 101 that is added in log file when the output verbose level for the merge agent is set to 4.
Peer to Peer Replication now supports partial updates made to text column using Update or .Write semantic.
Cleanup stored procedure has been redesigned to avoid redundant upload of data.
Reporting Services (aka SSRS)
Logging Message with Processing Errors and Warnings to Prevent common calls to CSS, Large HTML Reports.
Detection of the report design to determine if the report design will net a large HTML report and potentially cause performance issues. This should be easily detected by examination of the Interactive Page Height and Width settings.
You create a report in the Report Builder. You enter value '0' in the InteractiveSize property. You receive a pop-up window that contains the following message warning about potential performance impact:
Setting interactive heigth to 0 mat have a performance impact.
Several performance enhancements in Service Pack 2 as described in the following articles:
Performance problems occur when database lock activity increases in SQL Server 2012
Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012
Performance decreases after an ALTER INDEX…ONLINE operation is aborted in SQL Server 2012
sys.dm_db_session_space_usage reflects the amount of space taken in tempdb database by user object per session. When the number of rows deleted is beyond 1024 pages, the de-allocation of these pages are deferred. In SQL Server 2012 Service Pack 2, the DMV will also include space usage for allocation units that are deferred in a new column called user_objects_deferred_dealloc_page_count.
sys.dm_server_services will now correctly specify the node name in cluster_nodename column if SQL Server is installed on a failover cluster.
Ring buffer enhancements
The ring buffer for the app domains RING_BUFFER_CLRAPPDOMAIN includes the reason why the app domain was unloaded in the <DoomReason> field:
1 = unloaded by DDL operations such as drop assembly.
2 = unloaded by security related operation such as changing permission.
3 = unloaded because of memory pressure handling.
4 = unloaded because of failed initialization.
The corresponding Extended Event app_domain_ring_buffer_recorded includes the reason for unload in doom_reason event data field.
RING_BUFFER_RESOURCE_MONITOR has entries when a change is detected in the state monitored by Resource Monitor. Additionally, for system and process resource notifications, pool notifications are also available in the <IndicatorsPool> </IndicatorsPool> node.
Resource_monitor_ring_buffer_recorded extended event includes pool wide notifications in the pool_indicators data field.
RING_BUFFER_NONYIELD_PROCESSTABLE includes process name in addition to other information up to 8192 threads that are running in the system at the time of recording the ring buffer entry.
Backup and Restore Enhancements
Backup of tail log of model with norecovery option is no longer allowed starting with Service Pack 2.
Backup and Restore completion messages in error log includes duration and throughput information:
Following extended events have been added or modified in SQL Server 2012 Service Pack 2:
Extended events page_allocated and page_freed can be used to see the top consumers of SQL Server memory. Starting with Service Pack 2, these extended events also report reserved memory that is allocated and freed during query execution.
When a record is deleted in a table or index, such a delete operation never physically removes records from pages, it only marks them as having been deleted, or ghosted. This is a performance optimization that allows delete operations to complete more quickly. A background task called the ghost cleanup task then physically removes all the deleted records. Several extended events have been added in Service Pack 2 to provide insights into the various phases of this task:
For more information about how to determine the current SQL Server version and edition, click the following article number to view the article in the Microsoft Knowledge Base:
321185 How to determine the version and edition of SQL Server and its components