Category: Errors and Exceptions

The transaction log for database ‘database name’ is full due to ‘REPLICATION’

Today, we are going to see an exception from SQL Server “The transaction log for database ‘dbname’ is full due to ‘REPLICATION'”.

Recently, we received a backup of a database for a troubleshooting purpose from the production. The database had configured with CDC in Production environment. While we get this database and restored in our local environments, it is observed that the size of the database is very huge and if you look at the size in details, its log that is majorly contributing the size.

Since its huge in size, we tried to shrink the file, please note this is a non-production environment, shrinkfile is not advised to run without a careful consideration and validation. Few references on shrink file written earlier. Since, in our case it was a testing environment, we were free to use this command to reclaim the space.

Msg 9002, Level 17, State 6, Line 10 The transaction log for database ‘Restoredbname’ is full due to ‘REPLICATION’.

It is also observed that log_reuse_wait_desc was showing “REPLICATION” for the database.

Select log_reuse_wait_desc,* From sys.databases
As we know this db was enabled with CDC in Production environment, the first attempt was to disable CDC on restored database.

use Restoredbname
sys.sp_cdc_disable_db 

Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 262 [Batch Start Line 2] Could not update the metadata that indicates database Restoredbname is not enabled for Change Data Capture. The failure occurred when executing the command ‘(null)’. The error returned was 9002: ‘The transaction log for database ‘Restoredbname’ is full due to ‘REPLICATION’.’. Use the action and error to determine the cause of the failure and resubmit the request.

We also noticed checkpoint was also not successful due to serious disk issue.
checkpoint

Could not write a checkpoint record in database Restoredbname because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files. Msg 5901, Level 16, State 1, Line 10 One or more recovery units belonging to database ‘Restoredbname’ failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure. Msg 9002, Level 17, State 6, Line 10 The transaction log for database ‘Restoredbname’ is full due to ‘REPLICATION’.

Finally, we decided to apply sp_repldone on the database as below. When xactid is NULL, xact_seqno is NULL, and reset is 1, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log. sp_removedbreplication stored procedure removes all replication objects on the publication database on the Publisher instance of SQL Server or on the subscription database on the Subscriber instance of SQL Server. Once we executed the commands, we were able to shrink the file and the size has been reclaimed to os.

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, @reset = 1
sp_removedbreplication

The solution discussed is NOT a general solution nor for Production environments. This should ONLY be used for non-production or lower environments where the restored database is used for testing purpose and truncating log is not a concern!

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

How to capture deadlock occurrences in SQL Server using sys.dm_os_performance_counters

Today, let us quickly see how to monitor deadlocks in your SQL Server. There are multiple ways to get the deadlock information.

But, this blog post details an approach to capture the number of deadlocks using performance monitor dynamic management view in a SQL Server.

This approach does not really cover the deadlock graph capturing or analyzing the deadlock graph etc. This enables you to just understand if there are deadlocks in your system and it captures the data as you configured. This data can be used to generate nice reports as per your requirement.

Object creation script

First step is to create the objects to store the deadlock information. Please note, we are not doing to capture the deadlock information, but just the occurrence of deadlocks. So we need a table with DateAdded to denote the captured date and time and deadlocks column to denote the number of deadlocks occurred.
Drop Table  if exists DeadlockTracker

CREATE TABLE DeadlockTracker(
    DateAdded datetime NOT NULL
    , Deadlocks int NOT NULL
)


CREATE UNIQUE CLUSTERED INDEX IX_DeadlockTracker_DateAdded_U_C ON DeadlockTracker
(
    DateAdded
) WITH (FillFactor = 100)

Capture Query

The next step is to define the capture process. The idea is to frequently query a dynamic management view – sys.dm_os_performance_counter and log the data in the table as defined int he first step.

We need to capture these information in a defined interval, may be every 15 minutes. We can configure a SQL job to run every 15 minutes.
DECLARE @CounterPrefix NVARCHAR(30)
SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
                            THEN 'SQLServer:'
                        ELSE 'MSSQL$' + @@SERVICENAME + ':'
                        END

INSERT INTO DeadlockTracker(DateAdded,  Deadlocks)
SELECT DateAdded            = GetDate()
     , Deadlocks             = (SELECT cntr_value FROM sys.dm_os_performance_counters 
									WHERE object_name like @CounterPrefix + '%'
										AND instance_name IN ('', '_Total')
										AND counter_name ='Number of Deadlocks/sec')

Sample Report Dashboard Queries

This is the last step which is nothing but visualization of data captured. This is absolutely depending on data analyst discretion, but a very basic report sample has provided for your reference. You can change the query to get the data group by month/days/quarter etc.

Select *, Deadlocks - Lag(Deadlocks) Over(Order by DateAdded asc) From  DeadlockTracker

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

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.