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!!!

Advertisements

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!!!

SQL Server detected a logical consistency-based I/O error: incorrect checksum

Recently, we encountered an issue with a restored database stating the below error message:

Msg 824, Level 24, State 2, Line 28
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xafbb455a; actual: 0xaf9a455a).
It occurred during a read of page (1:310732) in database ID 161 at offset 0x00000097b98000 in file ‘F:\DATA1\database.MDF’.
Additional messages in the SQL Server error log or system event log may provide more detail.
This is a severe error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB).
This error can be caused by many factors; for more information, see SQL Server Books Online.

As the error message is pretty straight forward, a clear issue with some consistency (I/O error). And this has been even confirmed by running a DBCC CHECKDB. To resolve the issue, we decided to go with REPAIR_ALLOW_DATA_LOSS option as this is a lower environment issue. I personally do not recommend to use this option for production databases as there is a chance of data loss.


alter database <database>  SET single_user with rollback immediate 
dbcc checkdb('<database>',REPAIR_ALLOW_DATA_LOSS)
alter database <database>  SET multi_user with rollback immediate 

Before we do this option, just curious to understand the object being corrupted from the error message.

Using DM_DB_DATABASE_PAGE_ALLOCATIONS

The below code will give you information on the objects. Please note the below is supported only from SQL 2012 and later versions. For earlier version, you need to use DBCC PAGE commands.


SELECT DB_NAME(susp.database_id) DatabaseName,
OBJECT_SCHEMA_NAME(ind.object_id, ind.database_id) ObjectSchemaName,
OBJECT_NAME(ind.object_id, ind.database_id) ObjectName
FROM msdb.dbo.suspect_pages susp
CROSS APPLY SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS(susp.database_id,null,null,null,null) ind
WHERE allocated_page_file_id = susp.file_id
AND allocated_page_page_id = susp.page_id

Later, we identified the issue is associated with backup and we took a fresh backup of the database and restored. It worked like a treat!!!

ALTER DATABASE failed because a lock could not be placed on database ‘dbname’. Try again later.

Problem statement

As a DBA, you will be facing the error message “ALTER DATABASE failed because a lock could not be placed on database ‘dbname’. Try again later.” often for certain actions like taking database offline/setting single user etc.

Analysis

This is due to other processes locking your current operations.

Resolution

1. Find out the query which is blocking the current operation using the below query
Live monitoring query

2. Kill the process and take the database in SINGLE_USER

KILL 191 --session id from the first query related to the database
USE MASTER
ALTER DATABASE DB905 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

3. Take the database offline

ALTER DATABASE db905 SET OFFLINE WITH ROLLBACK IMMEDIATE

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/

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!!!