Category: Errors and Exceptions

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

Could not update the metadata that indicates database <> is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.

While configuring Change Data Capture on a database that is restored from different environment, we might end up with an error message as below:

Could not update the metadata that indicates database <> is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.

To resolve the issue, we need to change the owner of database and then enable CDC for the database as below. Please note that, the database would have restored irrespective of the error. You just have to run the below statement to avoid similar issues in future.

EXEC sp_changedbowner 'sa'
EXEC sys.sp_cdc_enable_db ;

Ref: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-changedbowner-transact-sql?view=sql-server-2017

Decoding hexa error code to windows error message in SQL Server

Problem Statement:
Sometimes, the error messages generated are not friendly to understand the underlined issues. This is going to be an extra effort to google out for most of us with the error code (mostly a hexa value) and identify the corresponding windows error message.

Solution:
A simple solution would be to use NET HELPMSG with the errocode. But if the error code is a hexa value, then we need to identify the corresponding Decimal value to use as a parameter to NET HELPMSG.

Here is a script to identify actual error message from hex error code. This is important to be aware that this approach will only work for Win32 error codes from Microsoft Windows. If the net helpmsg command does not return a result, the cause is likely the error has not originated from Windows, or no longer a valid Win32 process.

DECLARE @hex VARCHAR(64) = '0x80070643'
Set @hex = Right(@hex,4)

DECLARE @rez BIGINT;
SELECT @rez = ISNULL(@rez,0) * 16 + 
CHARINDEX(substring(@hex,n.number+1,1),'0123456789ABCDEF') - 1
FROM MASTER..spt_values n WHERE n.TYPE='P' AND n.number<len(@hex)

/*
	--To use NET HELPMSG, need to enable xp_cmdshell as below:

	EXEC sp_configure 'show advanced options',1
	RECONFIGURE WITH OVERRIDE
	EXEC sp_configure 'xp_cmdshell',1
	RECONFIGURE WITH OVERRIDE
	EXEC sp_configure 'show advanced options',0
	RECONFIGURE WITH OVERRIDE
*/

Declare @Table Table(WindowsErrorMessage nVarchar(MAX))
Declare @s nvarchar(500) = 'NET HELPMSG ' + Cast(@rez as varchar(MAX))
Insert into @Table 
exec xp_cmdshell @s

Select @hex HexaVal,@rez DecimalVal,* From @Table 
where WindowsErrorMessage is not null