DBMIRROR_DBM_EVENT wait type causing blocking in SQL Server

Recently, we observed lots of blocking in our production server showing the wait type as DBMIRROR_DBM_EVENT. To brief, the system is an OLTP system with lots of DML actions, ended up with lot of long running transactions and blocking the user transactions. As DBMIRROR_DBM_EVENT wait type is an internal to Microsoft and there is less documentation available on Web, we started our troubleshooting looking at the mirroring set up. Here are few points we started our analysis as below.

1. Verify DB Mirroring is working uninterrupted – if the mirroring is not working for some reason, on synchronous mode, the impact is huge, the principal server has to wait to commit.

2. Verify feasibility of DB Mirroring mode to Asynchronous – Changing Mirror mode from synchronous to Asynchronous is not a solution,however, its a workaround. Ideally, we need to identify if long running transaction/index maintenance etc caused the issue while applying the changes to Mirror.

3. Verify network latency between Principal and Mirror – The latency in the network is one of the reason that can be assessed using the perfmon counter.

Root cause and resolution

In our case, the root cause was the first one. For some reason, the mirroring has been broken, no endpoint was accepting the connection from the principal server. This lead to a situation on the principal server each time a transaction waits for its log (LSN) to be hardened on the mirror.Once we reestablished the mirroring, the wait type is removed and no blocking observed further.

How to get the status of Mirroring:


SELECT
DB_NAME(DATABASE_ID) AS [DATABASENAME],
CASE WHEN MIRRORING_GUID IS NOT NULL THEN 'MIRRORING IS ON' ELSE 'NO MIRROR CONFIGURED' END AS [ISMIRRORON],
[MIRRORING_STATE_DESC],
CASE WHEN MIRRORING_SAFETY_LEVEL=1 THEN 'HIGH PERFORMANCE' WHEN MIRRORING_SAFETY_LEVEL=2 THEN 'HIGH SAFETY' ELSE NULL END AS [MIRRORSAFETY],
MIRRORING_ROLE_DESC,MIRRORING_PARTNER_INSTANCE AS [MIRRORSERVER]
,MIRRORING_PARTNER_NAME AS [PARTNER NAME]
,MIRRORING_ROLE_DESC AS [MIRROR ROLE]  
,MIRRORING_SAFETY_LEVEL_DESC AS [SAFETY LEVEL]
,MIRRORING_WITNESS_NAME AS [WITNESS]
,MIRRORING_CONNECTION_TIMEOUT AS [TIMEOUT(SEC)]
FROM SYS.DATABASE_MIRRORING

See Also:
https://blogs.msdn.microsoft.com/grahamk/2011/01/10/sql-server-blocking-caused-by-database-mirroring-wait-type-dbmirror_dbm_event/

Advertisements

Error Message – The index ” is dependent on column ”.ALTER TABLE ALTER COLUMN ” failed because one or more objects access this column.

It is always annoying when we get a request to change the datatype of an existing column for many reason. The changes are due to business reason or bad design or anything. But, when we change to accommodate, we need to do lots of analysis to ascertain the impact of the change.

Here, we are going to explain a case, changing datatype size varchar (n) to varchar(max). This blog ONLY explains the context of changing the datatype not the impact of the change in terms of performance.


Create Table ColumnDatatypeChange (Col1 int,Col2 varchar(1))
Insert into ColumnDatatypeChange Select 1 , 'L'
create Index IX_ColumnDatatypeChange on ColumnDatatypeChange(Col1) include(Col2)
Alter Table ColumnDatatypeChange Alter column Col2 Varchar(MAX) -- This statement will fail

On executing the below snippet, we get the below error message. (Please note that an index is dependent on column)

Error Message:
Msg 5074, Level 16, State 1, Line 8
The index ‘IX_ColumnDatatypeChange’ is dependent on column ‘Col2’.
Msg 4922, Level 16, State 9, Line 8
ALTER TABLE ALTER COLUMN Col2 failed because one or more objects access this column.

However, when we do the change from MAX to 8000, it gets executed without any issue.


Create Table ColumnDatatypeChange (Col1 int,Col2 varchar(1))
Insert into ColumnDatatypeChange Select 1 , 'L'
create Index IX_ColumnDatatypeChange on ColumnDatatypeChange(Col1) include(Col2)
Alter Table ColumnDatatypeChange Alter column Col2 Varchar(8000) -- This statement will succeed

Reason:

The reason for this behavior is due to the change from non-max type to a max type. SQL Server considers this change as dropping a column and adding a new column. Internally, the non-max and max types do not share the same storage types (Row-Overflow vs. LOB allocation units). Hence, this change needs to update every row and move the data from dropped column to newly added column. However, if you see for non-max type change, they share the same storage (either in-row or in the row-overflow allocation unit), so no issues with in-place update, that mean, no need to drop the dependent(index in this case).

Resolution:

1. Drop the index and change the column size followed by adding the index back to the table


Drop Index IX_ColumnDatatypeChange on ColumnDatatypeChange
Alter Table ColumnDatatypeChange Alter column Col2 Varchar(MAX)
create Index IX_ColumnDatatypeChange on ColumnDatatypeChange(Col1) include(Col2)

2. Analyze and try to avoid the size to MAX (if possible)

Thanks for your read and looking for your feedback!!!

Msg 22841, Level 16, State 1, Procedure sp_cdc_vupgrade, Line 323 – While restoring from a cdc enabled database backup

We encountered an issue as below while restoring from a CDC enabled database backup.

Though Management Studio has shown the above message, the backup restore was successful. To further understand, we tried to restore the database using T-SQL, but ended with the same error.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 22841, Level 16, State 1, Procedure sp_cdc_vupgrade, Line 323
Could not upgrade the metadata for database ‘CDCTest’ that is enabled for Change Data Capture. The failure occurred when executing the action ‘alter cdc.change_tables index change_tables_unique_idx with (drop_existing = on)’. The error returned was 4922: ‘line 102, state 9, ALTER TABLE ALTER COLUMN column_id failed because one or more objects access this column.’. Use the action and error to determine the cause of the failure and resubmit the request.
RESTORE DATABASE successfully processed 1058229 pages in 72.909 seconds (113.393 MB/sec).

This error message was more clear that the restore database was successful.

In addition, we found that Microsoft Knowledge Base article KB2567366 describes a mechanism for skipping the internal “CDC Upgrade” process by enabling the trace -T3101. When the -T3101 trace flag is used, the restore process bypasses a CDC upgrade operation that is associated with this issue.


DBCC TRACEON (3101, -1)

RESTORE DATABASE ArchivalTest_1 FROM DISK = 'C:\bkpfile.bak'
WITH MOVE 'bkpfile' TO 'C:\data\bkpfile.mdf'
    , MOVE 'bkpfile_log' TO 'C:\data\bkpfile.LDF'
    , REPLACE
    , KEEP_CDC;

DBCC TRACEOFF (3101, -1)

The above query has executed successfully without any error message!!!

The multi-part identifier “” could not be bound. with insert statement to get non insert values into OUTPUT variable in SQL Server

Today, one of my colleague was asking how to get the non inserted values from the insert statement to output table variable.
Here is a sample code that he tried to get information. He reported there is an error message as below.

Msg 4104, Level 16, State 1, Line 12
The multi-part identifier “p.PId” could not be bound.

Sample code:


CREATE TABLE T1 (RID INT IDENTITY(1, 1), col1 INT, col2 INT)
CREATE TABLE T2 (PID INT IDENTITY(1, 1), col1 INT, col2 INT)
Declare @OutputTable Table (ID INT IDENTITY(1, 1), PID INT, RID INT, col1 INT, col2 INT)

INSERT INTO T2 VALUES (1, 1), (2, 2), (3, 3), (4, 4)

INSERT into T1 (col1, col2) 
	OUTPUT p.PId, inserted.RID, inserted.col1, inserted.col2
	INTO @OutputTable (PID, RID, col1, col2)
Select Col1, Col2 From T2 p

SELECT  * FROM    @OutputTable

DROP TABLE T1,T2

Solution:
While analyzing, it is clear that there is no straight way to get non-inserted values into OUTPUT clause.But, there is work around to get the information for this scenario by using MERGE and use the INSERT statement as below:


CREATE TABLE T1 (RID INT IDENTITY(1, 1), col1 INT, col2 INT)
CREATE TABLE T2 (PID INT IDENTITY(1, 1), col1 INT, col2 INT)
Declare @OutputTable Table (ID INT IDENTITY(1, 1), PID INT, RID INT, col1 INT, col2 INT)

INSERT INTO T2 VALUES (1, 1), (2, 2), (3, 3), (4, 4)

MERGE INTO T1 r USING T2 p ON 1 = 0
WHEN NOT MATCHED THEN
    INSERT (col1, col2) VALUES (p.col1, p.col2)
    OUTPUT p.PId, inserted.RID, inserted.col1, inserted.col2
    INTO @OutputTable (PID, RID, col1, col2);

SELECT  * FROM    @OutputTable

DROP TABLE T1,T2

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Hello friends, I don’t know what made me to write a blog, either it could be because i sit next to latheesh every day or inspired by reading his blogs daily, Any how, let me join with my Guru in sharing my experience.

we recently got a requirement to maintain the data integrity between two tables on a column which does not have any relationship between them.  Here is the scenario

we have two tables as listed below:



CREATE TABLE dbo.PEOPLE ( PEOPLE_ID INT ,EMPNAME VARCHAR(50), EMP_EMAIL VARCHAR(100))
CREATE TABLE dbo.USERS ( USERID INT ,USERNAME VARCHAR(50), USER_EMAIL VARCHAR(100))

INSERT INTO PEOPLE VALUES (1,'KIRAN','KIRANREDDY@GMAIL.COM')
INSERT INTO USERS VALUES (1,'KIRAN','KIRANREDDY2@GMAIL.COM')

I would like to maintain the same email in both tables for an employee, and email can be updated from either of these two tables.

So i have tried implementing the triggers on both tables when ever there is an update on email, I have to update the same email on both tables.

Here is the code I have implemented:



CREATE TRIGGER UPD_TRG_PEOPLE ON PEOPLE AFTER UPDATE AS
BEGIN
IF UPDATE(EMP_EMAIL)
UPDATE U SET USER_EMAIL=I.EMP_EMAIL FROM USERS U INNER JOIN INSERTED  I ON U.USERID=I.PEOPLE_ID 
END

CREATE TRIGGER UPD_TRG_USERS ON USERS AFTER UPDATE AS
BEGIN
IF UPDATE (USER_EMAIL)
UPDATE P SET P.EMP_EMAIL=I.USER_EMAIL FROM PEOPLE P INNER JOIN INSERTED  I ON P.PEOPLE_ID=I.USERID 
END

When I tried updating the email from people, It has resulted the following error:



Msg 217, Level 16, State 1, Procedure upd_trg_users, Line 3
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Solution :
Initially I have tried avoiding this error by changing the nested triggers Server Configuration Option


EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'nested triggers', 0 ;  
GO  
RECONFIGURE;  
GO

Even though this error has not appeared when I tried changing email, I feel that this is not an Ideal solution . As this is server level configuration which is applicable across  all databases. This may have other detrimental impact.

Hence i have found another solution with simple tweak in the trigger code:



CREATE TRIGGER UPD_TRG_PEOPLE ON PEOPLE AFTER UPDATE AS
BEGIN
IF UPDATE(EMP_EMAIL) AND  EXISTS (SELECT 1 FROM USERS   U INNER JOIN INSERTED I ON U.USERID = I.PEOPLE_ID WHERE I.EMP_EMAIL <> U.USER_EMAIL )
UPDATE U SET USER_EMAIL=I.EMP_EMAIL FROM USERS U INNER JOIN INSERTED  I ON U.USERID=I.PEOPLE_ID 
END

CREATE TRIGGER UPD_TRG_USERS ON USERS AFTER UPDATE AS
BEGIN
IF UPDATE (USER_EMAIL) AND  EXISTS (SELECT 1 FROM PEOPLE   P INNER JOIN INSERTED I ON P.PEOPLE_ID=I.USERID  WHERE P.EMP_EMAIL <> I.USER_EMAIL )
UPDATE P SET P.EMP_EMAIL=I.USER_EMAIL FROM PEOPLE P INNER JOIN INSERTED  I ON P.PEOPLE_ID=I.USERID 
END

Hope you enjoyed this post, please share your thoughts on the same.

The identifier ‘source column name’ cannot be bound. Only target columns and columns in the clause scope are allowed in the ‘WHEN NOT MATCHED BY SOURCE’ clause of a MERGE statement.

Problem Statement:

When you want to have a condition in a MERGE with source and target for WHEN NOT MATCHED clause, you may likely to get an error message as below, if you put the condition directly to the MERGE statement.

The identifier ‘source column name’ cannot be bound. Only target columns and columns in the clause scope are allowed in the ‘WHEN NOT MATCHED BY SOURCE’ clause of a MERGE statement.

Lets quickly check an example:


Drop table T1,T2
create Table T1(Col1 int, Col2 int)
Insert into T1 Values(1,1),(1,2),(1,3),(1,4),(3,1)--,(2,1)

create Table T2(Col1 int, Col2 int)
Insert into T2 Values(1,1),(1,2),(1,3),(1,5),(2,1)

Select * From T1

		MERGE T1 TG
			USING T2 SR
			ON TG.Col1= SR.Col1 and TG.Col2 = SR.Col2
			WHEN MATCHED THEN
				UPDATE
				SET TG.Col2 = SR.Col2
			WHEN NOT MATCHED BY TARGET THEN
				INSERT  (Col1, Col2)
				VALUES (Col1, Col2)
			WHEN NOT MATCHED BY SOURCE AND TG.Col1 = SR.COl1 THEN
				DELETE;

Select * From T1

Solution:

Here is a simple way of solving the mentioned issue.


Drop table T1,T2
create Table T1(Col1 int, Col2 int)
Insert into T1 Values(1,1),(1,2),(1,3),(1,4),(3,1)--,(2,1)

create Table T2(Col1 int, Col2 int)
Insert into T2 Values(1,1),(1,2),(1,3),(1,5),(2,1)

Select * From T1

		MERGE T1 TG
			USING T2 SR
			ON TG.Col1= SR.Col1 and TG.Col2 = SR.Col2
			WHEN MATCHED THEN
				UPDATE
				SET TG.Col2 = SR.Col2
			WHEN NOT MATCHED BY TARGET THEN
				INSERT  (Col1, Col2)
				VALUES (Col1, Col2)
			WHEN NOT MATCHED BY SOURCE AND TG.Col1 in (Select Col1 From T2) THEN
				DELETE;

Select * From T1

ALTER DATABASE failed because a lock could not be placed on database ‘DBNAME’. Try again later. in SQL Server

At times, we may need to take a database offline. And there is a chance that we encounter an error message like below.
How do we solve this issue and take the database offline?


Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'DBNAME'. Try again later.

Solution:
If there are any connections open, then there is a lock on the database while getting the database into Single user mode/taking offline.
To resolve the issue, as a first step you need to kill all the sessions on the database followed by the ALTER database statement.


USE master;

DECLARE @killSessions varchar(8000) = '';  
SELECT @killSessions = @killSessions + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('dbname')
EXEC(@killSessions); 

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE dbname SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE dbname  SET MULTI_USER;