.NET Framework execution was aborted Error in SQL Errorlog

SQLServerF1

There are many error messages defined in SQL Server which are returned when certain invalid operation is performed and based on the task performed, appropriate error number, severity and brief error message is sent to the application which run the query like SSMS or SQLCMD. Unfortunately, not all the error messages are understood easily and can become difficult to identify the cause and to find a solution. Especially errors related to performance or memory can be even difficult to diagnose or troubleshoot. One of the error, which I worked on recently was related to .NET Framework execution was aborted by escalation policy because of out of memory. Initial look at the error message, it given an idea that the error message was result of memory problem, as it is “out of memory” in the error message.

Error: 6532, Severity: 16, State: 49.
–> .NET Framework execution was aborted by escalation policy because of out of memory.
[WARNING] System.Threading.ThreadAbortException: Thread was being aborted.
[WARNING] System.Threading.ThreadAbortException:
[WARNING] at System.Data.SqlTypes.SqlChars.get_Value()
[WARNING] at System.Data.SqlServer.Internal.XvarWlobStream.GetCharArray()
[WARNING] at System.Data.SqlServer.Internal.XvarWlobStream.GetString()
[WARNING] at System.Data.SqlServer.Internal.XvarWlobStream.GetSqlString()

Other than the above error, you may also end up seeing some error messages like below.
– AppDomain 2 (xxx) is marked for unload due to memory pressure.
– AppDomain Trio.dbo[runtime].xx was unloaded by escalation policy to ensure the consistency of your application. Out of memory happened while accessing a critical resource.
– Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure.
– Additionally, you may see the above error messages with SQL Server 2008 features which use SQL CLR internally, like DMF (Declarative Management Framework) and spatial data types.

These errors can occur when SQL CLR stored procedures, user defined functions, user defined data types, or user defined aggregates, are used against SQL Server especially on 32 bit SQL instance.
– AppDomain 2 (xxx) is marked for unload due to memory pressure – This error message can be mostly informational message indicating that SQL CLR is responding to the memory pressure on the system. If this is an intermittent error message and is not affecting the execution of SQL CLR objects, the message can be ignored.
– For other errors, ensure that .NET Framework 2.0 is up to date with the latest available patch. Ensure SQL CLR object uses any assemblies other than those documented are not used. SQL Server 32 bit instance virtual memory is limited to 4GB. If we use SQL CLR extensively with a SQL Server 32 bit instance and experience one or more of above errors, then the best thing would be to migrate to a 64 bit SQL Server. This will allow SQL CLR to access more virtual memory and may prevent the above errors from happening in your environment. Other than that when we develop custom SQL CLR applications, we need to use SQL CLR memory carefully, like avoiding caching large amount of data using objects such as DataTables, If required use TSQL to retrieve the data, try avoid using static variables in your code to store large objects, and try create objects as late, and release them as early, as possible. If issue still persists, then check if there are any known issues and patch SQL Server to latest version.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Failed to Decrypt A Column Encryption Key Error in SQL Server 2016

SQLServerF1

There have been many new features introduced with SQL Server 2016 and you may get different errors while you use these new features. One such issue and errors DBAs might get while they try to use new SQL Server 2016 feature Always Encrypted data is as below.
Msg 0, Level 11, State 0, Line 0
Failed to decrypt column ‘test’
Msg 0, Level 11, State 0, Line 0
Failed to decrypt a column encryption key using key store provider: ‘MSSQL_CERTIFICATE_STORE’. The last 10 bytes of the encrypted column encryption key are: ‘F1-S3-K2-8E-69-E7-F7-O8-1W-B4’.
Msg 0, Level 11, State 0, Line 0
Certificate with thumbprint ‘664EBAB58B3A8150AC51E2BEF40BDEC17055167C0′ not found in certificate store ‘My’ in certificate location ‘CurrentUser’.
Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store.
Parameter name: masterKeyPath

This error occurs if we do not have certificate imported to the local machine’s certificate store. Always Encryped has two main keys, one is column master key, which is stored in the trusted key store and its information about column master keys, including their location, is stored in the database in system catalog views. Another important part is Column encryption keys, which actually encrypts the sensitive data. For clients to be able to access the data, there needs to be a certificate which needs to be installed on the client systems, with out which we will get the errors related to the certificate. This is to allow only authorized users with the certificate to be able to access and decrypt the sensitive data.

Apart from this error for not importing the certificate, there can be other errors too returned with Always Encrypted even when the certificate in installed or imported on the client system like below,
Failed to decrypt a column encryption key using key store provider: ‘MSSQL_CERTIFICATE_STORE’.
The last 10 bytes of the encrypted column encryption key are: ‘F1-S3-K2-8E-69-E7-F7-O8-1W-B4’. Certificate specified in key path ‘CurrentUser/My/664EBAB58B3A8150AC51E2BEF40BDEC17055167C0’ does not have a private key to decrypt a column encryption key. Verify the certificate is imported correctly.
Parameter name: masterKeyPath

The above error occurs even when we import the certificate because the imported certificate only has a public key, which is not enough to decrypt the data. We will also need a private key to be able to successfully decrypt the data.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

The query processor ran out of internal resources Error in SQL Server errorlog

SQLServerF1

There are many number of errors documented for SQL Server and are raised when certain error condition occurs which range from syntax errors to problems with instance, databases, jobs, performance, features, etc. When a particular error is received, the error message will have some information regarding the kind of error and what might have caused the error. Sometimes, it is found that the errors are not detailed or misleading and make troubleshooting move in wrong direction or can cause slowdown of resolution the errors.

Out of many errors, some errors are related to performance and the error message may not be enough to understand the reason of the failure. Below is one such error message which occurs due to performance issues with the server performance or with a specific stored procedure or a batch or a individual SQL query. When ever a query hits SQL Server instance, a query plan is created if it is the first time, if not, SQL server optimizer will check for existing plans and reuse if one is already present. There are various reasons the query plan generated may not be optimal and takes more time to complete and sometimes the query can fail altogether with different errors. Below is one such error.

Error: 8623, Severity: 16, State: 1.
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

This particular error mentioned above is is a rare event and only expected to occur when a very extremely complex queries or queries that reference a very large number of tables or partitions is received by SQL Server. There can be different reasons on what a complex query is referred as, it can happen when attempting to select records through a query with a large number of entries in the “IN” clause like greater than 10,000. There are also some bugs or known issues that can cause this error and some connect bugs are raised too. You can use SQL Server profiler or extended events to track and identify the query causing this error. If this error is recurrent and happening on certain day or time, then we can check if there are any jobs running at that time leading to this error.

The resolution for this error is to simplify and rewrite the complex query. We can also try and divide and get part of the query working and then use extra joins and conditions. We can also use temp table and use temp tables in joins to avoid this error from occurring. We can also try other alternatives like we can try to run the query using the hint option like force order, or option hash join or merge join or using trace flag 4102 with a plan guide. Enabling Traceflag 4102 will revert the behavior of the query optimizer to older version of optimizer for handling semi-joins.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Infinite Recompile Warning Messages in SQL Server Errorlog

SQLServerF1

Sometimes you may notice below warning messages in SQL Server Errorlog
A possible infinite recompile was detected for SQLHANDLE 0x020000006E8C161EA193363B55EBDF5A21857C439998726E, PlanHandle 0x06000B006E8C161E40017CDE0A0000000000000000000000, starting offset 0, ending offset -1. The last recompile reason was 6.
It is not so eay to troubleshoot this error as this may happen due to various different reasons and sometimes it may be intermittent and gets resolved itself or sometimes it happens continuously until fixed. These messages sometimes may also be logged due to changes to statistics causing recompile and can be ignored.

This message is logged to SQL Server errorlog if one or more SQL statements caused the query batch to recompile at least 50 times or more. The specified statement should be corrected to avoid further recompilations. In the warning message you will find the SQLHandle and planhandle which can be used to find the query or batch that is causing this warning message to be logged to SQL errorlog. Also, there is another important piece of information mentioned in the warning message, which is “The last recompile reason was %d”. Below are the possible recompile reason code and associated description.

Reason code

Description

1

Schema changed

2

Statistics changed

3

Deferred compile

4

Set option changed

5

Temp table changed

6

Remote rowset changed

7

For Browse permissions changed

8

Query notification environment changed

9

Partition view changed

10

Cursor options changed

11

Option (recompile) requested

The most common recompile reason is the recompile code 2. From the table above, we can see that this occurs due to statistics-based recompile. If statistics on a table accessed by a SQL query or batch changes, a recompile for the query will be triggered, and if they change often enough, then you could legitimately see this warning in the error log. Therefore, if you see the infinite recompile message appear in the errorlog with reason equal to 2, and you know the statistics on your tables are likely changing often (because the doing a lot of data modification), you can simply ignore the warning.

If the recompile reason was 6, it will be related to remote rows changing on linked server. If this warning message appears continuously, then to resolve this, you may need to look into code and take appropriate action. Some of the possible resolutions for this include selecting the remote rowset into a local temporary table before joining with the remote data, this way the statistics will be available, locally. This may also lead to performance issues that you may be observing in your environment, so bringing locally may also resolves the performance issue we are experiencing.

The other recompile messages are not so often and are self explanatory. You can find the SQL batch or statement causing the infinite recompile by running below query and passing plan_handle, starting_offset and ending_offset which you can find in the warning message logged.

SELECT DB_NAME(st.dbid) AS database_name
, OBJECT_NAME(st.objectid) AS object_name
, st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (sql_handle) AS st
WHERE qs.statement_start_offset = starting_offset
AND qs.statement_end_offset = ending_offset
AND qs.plan_handle = plan_handle;

Once you know the statement, you can now check the code to make the required adjustments to avoid this message from occurring.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

List of Bugs Fixed in SQL Server 2012 SP1 – Part 10

SQLServerF1

With every release of SQL Server product or subsequent service packs or Cumulative updates, new bugs are encountered or discovered. Below are some of the bugs which were fixed with the release of SQL Server 2012 Service Pack 1 (SP 1) for Microsoft SQL Server 2012.

SQL Server MSDN Bug Number 861377
You cannot use the SQL Server slipstream package to update the current feature to Service Pack 1 when you add a new feature at the same time.
SQL Server MSDN Bug Number 846622
The Install wizard freezes when it searches for product updates.
SQL Server MSDN Bug Number 817937
FIX: Master Data Services (MDS) feature is visible in the feature tree when you try to uninstall SQL Server 2012 SP1.
SQL Server MSDN Bug Number 954667

FIX: When you try to update the SQL Server Client Tools or SQL Server Full-Text Search feature by using a corrupted installer file, the feature does not display the correct error message.
SQL Server MSDN Bug Number 819093
Feature selection is unavailable when you install a security update for shared components when the instance component is at a different service pack level.
SQL Server MSDN Bug Number 907647
FIX: Updates SQM to report the service pack level as a number.

SQL Server MSDN Bug Number 887062
FIX: SQL Server service pack applies English to certain features even though these features are installed by using a localized language.
SQL Server MSDN Bug Number 1018645
“Exit code:-2068578302” error message when you try to uninstall an instance of SQL Server 2012.
SQL Server MSDN Bug Number 829208
Reporting service is in a bad state after a SQL Server upgrade fails.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 
1 2 3 67