Category: Errors and Exceptions

Error Message in SQL Server: Conflicting locking hints are specified for table “tablename”. This may be caused by a conflicting hint specified for a view.

Recently, One of my colleague has reported an issue as below from production environment.
Msg 4138, Level 16, State 1, Line 19 Conflicting locking hints are specified for table “tablename”. This may be caused by a conflicting hint specified for a view.
Error message is very clear and we can easily understand there are some incompatible hints applied in the same query caused a conflict. Let me quickly reproduce the issue for all of us to understand better with a simple example.
-- Sample tables and data population
Drop Table if exists T1,T2
GO
create Table T1(Col1 int )
create Table T2(Col1 int )
GO
Insert into T1 Values(1)
Insert into T2 Values(1)
GO
Select * From T1
Select * From T2
The above code will create two tables – T1 and T2. I do not want to get complicated with more columns and its a simple and easy replication with a single column Col1. Now, Let us create a view using both of the tables. Please note, while creating the view we have given WITH (NOLOCK) on one of table called T2 as below.
--Sample view 
Drop view if exists vw_test
GO
Create OR ALTER View vw_test 
as 
Select * From T1 
     where exists(Select 1 From T2 with (nolock) where t1.Col1 = t2.col1)
If you notice the VIEW, as mentioned above, NOLOCK has given to avoid any locking scenario deliberately. We are not going to cover whether NOLOCK is good or bad in this blog post, but this is a common bad practice that almost all people do, even experience people would do it at times. A view is a generalized form that anyone can use it as they like later. So, in this case, let us see some of the usages of the view to explain the behavior.
-- SELECT with intended lock
Select * From vw_test A with (UPDLOCK,HOLDLOCK) 
This is an example of intended lock on view. While executing the SELECT statement, we will end up with error message “Conflicting locking hints are specified for table “T2″. This may be caused by a conflicting hint specified for a view.”

Another example:
--Rowlock will conflicts with NOLOCK
Update A Set A.Col1  = A.Col1 + 2 
From vw_test A with (ROWLOCK) 
This is another example of ROW lock on view for an UPDATE query. While executing the UPDATE statement, we will end up with error message “Conflicting locking hints are specified for table “T2″. This may be caused by a conflicting hint specified for a view.”

So, precisely, there are many combinations that NOLOCK could end up with this similar exception.

Few are: ROWLOCK/TABLELOCK/UPDLOCK/SERIALIZABLE etc.

How to resolve this issue?

Two ways you can avoid issue as below:

1. Change the view definition to remove NOLOCK

Create OR ALTER View vw_test 
as 
Select * From T1 
     where exists(Select 1 From T2 where t1.Col1 = t2.col1)
--Rowlock will conflicts with NOLOCK
Update A Set A.Col1  = A.Col1 + 2 
From vw_test (TABLOCKX) A

2. Change the query that uses view to remove the conflict lock hints

--updlock will conflicts with NOLOCK
Update A Set A.Col1  = A.Col1 + 2 
From vw_test  A

Summary:

This is a simple error message and does not really require any extra thoughts to be put in to understand the error message or conflict scenario. This is not just for NOLOCK, any incompatible hints would cause this issue , however, I would like to take an important point that many of us are using NOLOCK as a free tool without knowing its implications. With this example( its just an another example), I would like to reiterate DO NOT use NOLOCK as a free tool, use it with atmost careful evaluation and understanding!

Iā€™d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

The enlist operation failed (reason: SQLServerAgent Error: The target server cannot establish an encrypted connection to the master server ‘Servername’. Make sure that the MsxEncryptChannelOptions registry subkey is set correctly on the target server.) (Microsoft SQL Server, Error: 22026)

While configuring SQL Server Multi Server Administration: Master and Target Servers in SQL Agent, we encountered an error as below:

TITLE: Microsoft.SqlServer.Smo
——————————

MSX enlist failed for JobServer ‘TargetServer’.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17289.0+((SSMS_Rel_17_4).181117-0805)&
EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

The enlist operation failed
(reason: SQLServerAgent Error: The target server cannot establish an encrypted connection to the master server ‘MasterServer’.
Make sure that the MsxEncryptChannelOptions registry subkey is set correctly on the target server.) (Microsoft SQL Server, Error: 22026)

Root cause Analysis & Resolution

As per the investigation, it is identified as an issue associated with a registry value on the target server MsxEncryptChannelOptions. When we configure the set up, it tries to establish the connection between master and targets in a secure channel with full SSL encryption. And if SSL encryption is not enabled between servers/instances, then this setting has to be changed in target servers using the registry. You can change the registry value here: \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\SQLServerAgent. Please note the default value is 2. We need to change it to 0, however, this change needs to be verified and evaluated with you security norms and standards.

Refer Also:

Set Encryption Options on Target Servers

Curious case of varchar to uniqueidentifier in SQL Server

Converting a datatype to another datatype is a very common requirement in real world and we usually do it with CAST or CONVERT (eg. int to char or varchar etc).

There are two types of conversions – implicit and explicit.

Explicit conversions are the conversions done by developers using SQL server functions like cast/convert etc and Implicit conversions are mainly managed by SQL Server internally. If you want to see these types of conversions, you can check for IMPLICIT_CONVERT in the execution plan generated by SQL Server.

Today, we are going to see a curious case of an implicit conversion – varchar to uniquidentifier.

SQL Server does a implicit conversion from varchar to uniqueidentifier by default as below:

Declare @varchar nvarchar(MAX), @uniqueidentifier uniqueidentifier
Set @varchar ='f124656c-136b-4beb-ab3a-b348053f898a,7f0b0dd5-22bc-421b-9416-3a7c24146a98'

Set @uniqueidentifier = @varchar
Select @uniqueidentifier

Now, if you look at the results, you can see the first 36 character is being converted implicitly. Since its a trivial, the operator may not be able to see in your execution plan.

The word of caution

Sometimes, if we are assigning more values/characters in the varchar variable, it would ONLY pick the first 36 characters and ignoring the rest characters by default. This default character of the conversion may end up with a wrong results, however, there would not be any sign of error! So beware of this characteristic while you writing a code.

“The database is encrypted by database master key, you need to provide valid password when adding to the availability group.”

Recently, one of my colleague has encountered an issue an error while adding a database to availability group as below:

Error Message:
“The database is encrypted by database master key, you need to provide valid password when adding to the availability group.”

Reason:

The error message is self explanatory one as this database does not meet a requirement to add AG as this database is protected with a master key. We may need to provide the master key while configuring the database into AG.Please note that this is not due to TDE, but due to master key.

One observation is that this is recently added as a validation in SQL Server 2016 version. Earlier version, you would be able to add seamlessly.

Solutions:

There are two solutions to add these databases into AG:

1. Use T-SQL to add the database into AG


ALTER AVAILABILITY GROUP AGName ADD DATABASE DBName

2. Provide the password and use GUI with few extra steps

In GUI, this is not so clear for the user that the password can be provided directly and configure AG.

Double click in the Password column
Type the password in the column
Click Refresh (To enable check box for the database)
Check the checkbox for the database.
Click Refresh again (To enable the Next button)
Click Next to progress

Length of LOB data to be replicated exceeds configured maximum 65536 in SQL Server

Problem Statement:

Sometimes When we do a DML operation in SQL Server, we end up with the below error –

“Length of LOB data to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit, other that the limit imposed by the data type. The statement has been terminated.”

From the error message, its clear that the operation has been terminated by SQL Server.

Analysis:

I would say, this is a clear error message that means it contains the error information, steps to resolve the exception and its implication. Let us expand the error message a bit more than what its provided for our clear understanding with this blog post.

Details:
Length of LOB data to be replicated exceeds configured maximum 65536.

This means that the length of a LOB data is exceeding the configured value of 65536 which defaults to 65536. There is a configuration “max text repl size option” in SQL Server which can be viewed by sp_configure system procedure or querying sys.configurations system table. By default, the value of the configuration has been set to 65536 in SQL Server.This has an impact on systems configured with replication, Change Data Capture. The configuration “max text repl size (B)” is not an advanced configuration, so you do not need to reconfigure with “show advanced options” to see this setting.

From the above picture, the description of max text repl size (B) configuration is clear that it denotes the maximum size of a text field in replication. However, we need to understand the value is also applicable for LOB datatypes as well. The issue can be raised whenever a LOB data that contains more length than the specified value for this configuration. The operation gets terminated for the same reason.

What do we do when we get this error?

The simple solution is to set the configuration “max text repl size (B)” to “-1”. This will ensure there is no limit other than imposed by the datatype of the column. However, this may have some detrimental impact on replication as the size increases we need to make sure the replication has no other side effects due to the size of the data to be replicated over the network etc.

1. Identify the LOB datatype column in the table
2. Assess the size or length of the data in the table and confirm that the size of the data is exceeding the configured value of “max text repl size (B)”
3. Identify if there are any replication/CDC configured with the table/database
4. If Change data capture does not require the LOB column to be tracked, avoid the column from capture by specifying ONLY required columns in captured_column_list for the table while configuring CDC for the table
5. Assess your data and conclude the maximum size of data it can reached up to as per the business requirement. As an example, a photo field can be restricted with a specified size through application that can be uploaded etc.
6. Set the required or expected size for max text repl size (B) configuration if possible instead of maximum -1

Note: We recently observed similar issue with a database configured AlwaysOn-Availability Group.