Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

Introduction

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.

More Information

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.

VSTS bug number

KB article number

Description

1374617

2028830

FIX: Shared components are installed in the wrong location when you implement a SQL Server 2008 or SQL Server 2012 Failover Cluster installation

1703014

2963382

FIX: Can't connect to the server when you run a cube creation MDX query in SSAS 2012

2011247

2927741

FIX: Image is not displayed correctly in the webpage when you use DrillThrough action on an SSRS 2012 report

2034994

2963383

FIX: Text truncated when you use HPB renderer to view a report in SSRS 2012

1769469

2963384

FIX: SQL Server crashes when the log file of tempdb database is full in SQL Server 2012

1973168

2963386

FIX: SSRS 2012 fails with IsolatedStorageException when subscriptions exported to Excel worksheets are larger than 10 MB

1973196

2963388

FIX: SSRS 2012 cannot export a report that is larger than 10 MB to Excel format

787358

2963404

FIX: Rollback causes database to go to suspect mode in SQL Server 2012

1043717

2806979

FIX: Poor performance when you run a query against a table in an RCSI-enabled database in SQL Server 2012

1178585

2963412

New DMF sys.dm_fts_index_keywords_position_by_document in SQL Server 2012 SP2

1182241

2966520

FIX: New log files are not created when you restart the Reporting Services Windows Service in SSRS 2012 on January 1

1240848

2966519

FIX: No error message when log backup failed in SQL Server 2012

1253861

2936004

FIX: Visual Studio 2012 crashes because of access violation when the data source binding is completed in Report Viewer 2012 UI in local mode

1262029

2966522

FIX: File Share subscription doesn't populate the description column in SSRS 2012

1481773

2966523

FIX: The "Name" drop-down list is empty when an SSRS 2012 report is specified

1419367

2964762

FIX: Event ID 22 or error 0xC1000016 will be logged when you use Discover command in SSAS 2012

2103903

2972285

FIX: Performance issues when you use SSISDB as your deployment store in SQL Server 2012

2021581

2965035

SSAS 2012 logging feature improvement in SQL Server 2012 Service Pack 2

203749

2952444

FIX: Poor performance when you use table variables in SQL Server 2012

2095064

2936328

FIX: Access violation occurs when you run CHECKTABLE or CHECKDB against a table that has persisted computed columns in SQL Server 2008 R2 or SQL Server 2012

1222472

2827857

FIX: DTA does not work on SQL Server 2012 instance that uses a supplementary character collation

2124209

2939277

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

Description

950185

Improved messaging around processing errors and warnings when you work with large HTML reports.

950189

Improved messaging around processing errors and warnings when you work with reports with large number of parameters.

994470

Incorrect results when you work with block mode unary operators.

1041277

DPM’s full backup on the server with AlwaysOn secondary will be converted to copy_only backup.

1278767

The value of tabular instance perfmon counters: Memory\VertiPaq <TypeName> KB is always 0.

1355346

The end SCOPE statement does not match the opening SCOPE statement.

1374617

FIX: Shared components are installed in the wrong location when you implement a SQL Server 2008 Failover Cluster installation

1610447

Uninitialized object leads to crashes and potentially wrong results when executing DAX expressions with conditional calculations.

1610492

A simple DAX query returns unexpected results when you use PowerPivot v2 11.0.3000.0.

2060218

"ACTIONABLE_HEAP_CORRUPTION" when SSRS configuration file is parsed by using an invalid InstanceId.

2065570

NonYieldProcessTable record in RING_BUFFER_NONYIELD_PROCESSTABLE ring buffer does not have user and kernel process time.

2075161

Provides platform information in the log of SSRS 2012

1347204

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

1184332

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"

1243853

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.

1404999

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.

1190211

Installation of SQL Server by using InstallShield ends up in a hang state. For more information, go to thisKnowledge Base article.

1386073

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

Connect Bug ID

Description

714689

Evaluation results are written to output xml file multiple times when you implement Enterprise Management Framework against multiple servers and an error occurs against one of the servers in the list.

735543

Database goes into a restore mode when its backup is restored as a different database on the same instance.

736509

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.

740181

SSMS does not fully manage Full-Text in SQL Server Express.

745566

SQL Server SMO ignores default constraint in SQL Server 2012 and SQL Server 2014.

764197

SSMS handles Numbered Stored procedures inconsistently.

769121

"Column '<column name>' does not belong to table summary. (System.Data)" error message when replicating tables have the same names but are located in different schemas.

773710

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.

774317

SSMS occasionally crashes on close, which then causes it to automatically restart

785064

"Value of 'null' is not valid for 'stream' " error message when you work with customer pipeline components in Business Intelligence Development Studio (BIDS).

785151

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.

791929

"A system assertion check has failed" error message when you insert data into a partitioned view that has triggers.

797967

Create script duplicates the statements when scripting column level permissions in SSMS.

799430

SSMS may crash when you try to refresh the SSMS window icon on the task bar.

804901

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.

805659

"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):

  • Supportability:

    • Message 35285 is displayed as an informational message as expected and not as an error.

  • Functionality:

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


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


  • SSISDB deadlock and cleanup performance improvement

    • Introduction

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

    • Architecture

      • Previous Model:

        • Each transaction creates a certificate and symmetric key, which must also be stored and processed in maintenance or cleanup tasks.

      • New Model:

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

      • Change summary:

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

      Notes:

      • 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:sys. dm_fts_index_keywords_position_by_document
      (
      DB_ID('database_name'), OBJECT_ID('table_name')
      )

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


  • Supportability

    • Transactional Replication:

      • 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:

        • Table Name

        • Primary Key column name and values

        • Current version

        • Pre-version

        • Post-version

    • Merge Replication:

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

  • Functionnality

    • Transactional Replication:

      • Peer to Peer Replication now supports partial updates made to text column using Update or .Write semantic.

    • Merge Replication:

      • Cleanup stored procedure has been redesigned to avoid redundant upload of data.


  • Logging Message with Processing Errors and Warnings to Prevent common calls to CSS, Large HTML Reports.

    • Overview:

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

    • User scenario:

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


  • Performance enhancements

    • 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

    • DMV enhancements

      • 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:

        • BACKUP DATABASE successfully processed 298 pages in 0.026 seconds (89.261 MB/second).

        • RESTORE DATABASE successfully processed 298 pages in 0.016 seconds (145.050 MB/second).

    • Extended Events

      • Following extended events have been added or modified in SQL Server 2012 Service Pack 2:

        • Memory Allocation:

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

        • Ghost Cleanup:

          • 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:

            • ghost_cleanup_task_start

            • ghost_cleanup_task_suspend

            • ghost_cleanup_task_packet_enqueue

            • ghost_cleanup_task_process_packet

            • ghost_cleanup_task_process_pages_for_db_packet


References

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

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×