Category: Errors and Exceptions

Change Data Capture, Errors and Exceptions

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

Advertisements
DBA Utilities, Errors and Exceptions, SCRIPT GALLERY

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
DBA Utilities, Errors and Exceptions

SQL Server Configuration Manager – The remote procedure call failed. [0x800706be]

Problem Statement:

Recently, I encountered an issue as below with SQL Server Configuration Manager which is a very common issue for most of us. Most of the time, this type of error is related to not having enough permission to see the SQL Server Configuration Manager. However, the case was different for me as I am the administrator for my system.

Observations:

In my laptop, I have multiple SQL versions like 2008 R2, 2014, 2016 and 2017.For some reason, SQL Server Configuration Manager (by default it was pointing to oldest one, nothing but 2008 R2 version) was not able to correctly get the information for all the installed versions. This was causing an issue.

To resolve the issue,

1. we can identify the file location as “C:\windows\syswow64\” OR “C:\Windows\System32\”.
The SQL Server Configuration manager file name should be similar to SQLServerManager**.msc where ** denotes the version of the SQL Server.

2. Open the latest version of SQL Server configuration manager file. This will work as expected.

However, the above is not a complete solution while we open the configuration manager from the start window. This is because, the default configuration manager will still be pointing to the older version of the SQL Server. To change this behavior, we need to do the following steps.

1. Open the “SQL Server Configuration” in start and right click on the icon.


2. Right Click on “SQL Server Configuration Manager” and change the Target file to the latest mmc file.(You should have administrator privilege to do so)


3. Close the Window

From then, you will be successfully able to see the SQL Server Configuration manager that fetch the data as expected.

Errors and Exceptions, Linked Server

Error Message: The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “” was unable to begin a distributed transaction.

Problem Statement:

We had an issue with Linked Server in one of our environment while executing a functionality. The error message is shown as below.

OLE DB provider “SQLNCLI11” for linked server “” returned message “The transaction manager has disabled its support for remote/network transactions.”.
Msg 7391, Level 16, State 2, Procedure “VIEWNAME” Line 8 [Batch Start Line 1]
The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “” was unable to begin a distributed transaction.

Analysis:

While analyzing, we identified the reason for the error is actually an INSERT operation on a view.To be more clear, lets discuss the scenario with an example as below.

Lets assume we have two servers ServerA and ServerB connected through linked server “LKSERVER”.

1. Create a table Called – dbo.TestLinkedServerTargetonPrimary in ServerA

Create Table dbo.TestLinkedServerTargetonPrimary(Col1 int)

2. Create a view called vw_LinkedServerTest in ServerB

create view vw_LinkedServerTest
		as
			Select Col1 From [ServerA].DatabaseA.dbo.TestLinkedServerTargetonPrimary

3. Create an INSTEAD OF Trigger for INSERT operation on the view created above

create TRIGGER [dbo].TR_LinkedServerTest on [dbo].vw_LinkedServerTest
		INSTEAD OF INSERT
		AS		
		BEGIN
                	INSERT INTO [ServerA].DatabaseA.dbo.TestLinkedServerTargetonPrimary
	                (
				Col1
                	)
	                SELECT 
				Col1
        	        from inserted
		END

4. Try inserting data into Linked Server table through view.

Insert into vw_LinkedServerTest Select 1

The insert operation is failing with the below error message.

OLE DB provider “SQLNCLI11” for linked server “LKSERVER” returned message “The transaction manager has disabled its support for remote/network transactions.”.
Msg 7391, Level 16, State 2, Procedure TR_LinkedServerTest, Line 7 [Batch Start Line 21]
The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “LKSERVER” was unable to begin a distributed transaction.

The error is due to the transaction created by the INSTEAD OF Trigger on the view. Though, we dont have any explicit transaction defined, the instead of trigger is creating an implicit transaction. As the transaction scope is across linked server, it tries to open a distributed transaction in the trigger and it fails due to non access to network DTC access.

Lets quickly confirm the cause of the issue by modifying the trigger code to get the transaction count.

From the above, we can clearly see the transaction count is increasing as the trigger is getting executed.

Solution:

There are two options to solve the issue.

1. Provide enough security or enable the configuration at Network DTC access

a. Open “Component Services” in both servers and change the security settings as below.

b. Once the settings are changed, try to execute the INSERT query.

One drawback of this solution is a high dependency on MSDTC. Unless there are no alternatives, I would not suggest to use this method .

2. Try to Avoid the Trigger

Yes, As already discussed,INSTEAD OF trigger on the view is causing the issue. If we can drop the trigger on the view, it will not create a distributed transaction and the query will be executed successfully.

Hope this post helps you for similar situations, please share your feedback/comments….

DDL, Errors and Exceptions

How to create a lock on a table that prevents other requests in SQL Server

Problem Statement:

Today, I got a very peculiar request from one of my colleague that he wants to put a lock on a table so that no other requestscan be served. The request was looking weird to me initially as most of us want to avoid locking/blocking scenarios in a system, but the request was looking for creating one. But Why? After asking many questions, I got his intention that he is looking for replicating a locking scenario in his local environment that ends up with a Timeout.

Solution:

The solution provided is simple but put a Schema modification lock(Sch-M) on his table. Schema modification lock will be acquired when a DDL statement is executed on the table. As Schema modification is not compatible with any other locks, it will prevent access to the locked object data. An example of the Sch-M lock is lock during index rebuild (Please note when used with ONLINE option, index rebuild will acquire Sch-M lock shortly at the end of the process).


Session 1:
--Create Table
create Table TestLock(Col1 int)
Insert into TestLock values ('100')

--Raise a Schema modification lock
begin Tran T1
ALTER TABLE TESTLOCK ALTER COLUMN COL1 VARCHAR(5000)

-----------------------------------------------------------------------
Session 2:
Select * From TestLOCK(NOLOCK)--NOLOCK has no impact on SCH-M locked object.

Thoughts:

Finally, I understood he is trying to put some code to handle the locking situation, but not the solution for the timeout. As he mentioned the issue happens ONLY when maintenance window is running, I believe this is something related to any maintenance operations like (rebuild index,update stats etc.). He can , later, better of find the actual issue and fix it like move the functionality out of the maintenance window!!!

Errors and Exceptions, TEMPDB

Could not open file E:\TempDB\tempdb.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.)

Recently we faced an issue that SQL Server was not coming up online in one of our environment.

We found below error messages in Windows application event logs:

Open failed: Could not open file E:\TempDB\tempdb.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
Unable to open the physical file "E:\TempDB\tempdb.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
CREATE DATABASE failed. Some file names listed could not be created.

As we have a clear message, while SQL Service starts, it was not able to create the TEMPDB as the mdf file is being used by another process.
When it happens, it will not start the SQL Service even we manually start it through services.msc. However, we identified, the service will come online once we restart the server.

We started our investigation verifying whether more than one instances are pointing to the same directory for TEMPDB files. Though we have multiple instances on the environment, we made it sure those instances are configured separately and properly. Once we confirmed the SQL Server configuration, we took our attention to other services especially with AntiVirus services. We found that there is Mcafee software running, but unfortunately, we could not find any reference on ProcessExplorer as well. However, we decided to stop the McAfee and see if the issue is related to the AnitVirus scan. Now, it is almost more than two weeks and we did not experience the issue.

Takeaway, if you find similar issue, Instead of disabling or stopping the services related Antivirus, we can exclude certain files associated with SQL Server from the scan.

Here are few directories that can be excluded from anti-virus scanning tool:

1. C:\Windows\System32\LogFiles
2. C:\Windows\System64\LogFiles
3. C:\windows\System32\GroupPolicy\
4. C:\windows\temp
5. C:\windows\SoftwareDistribution\Datastore
6. C:\windows\SoftwareDistribution\Datastore\Logs
7. C:\\Temporary ASP.NET Files
8. C:\Users\\AppData\Local\Temp
9. C:\Users\Default\AppData\Local\Temp
10. C:\ProgramData\Microsoft\SQL Server
11. C:\Program Files\Microsoft SQL Server
12. Exclude SQL Server Data Files (*.MDF, *.LDF, *.NDF)
13. Exclude SQL Server Backup Files (*.TRN, *.BAK)
14. Exclude Full-Text Catalog Files (*.SLS)
15. Exclude sqlmangr.exe
16. Exclude sqlservr.exe

Please give your feedback if this post helps you!!!