Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu

 Windows OS Hub / SQL Server / Find and Remove Locks in Microsoft SQL Server

March 3, 2023 SQL Server

Find and Remove Locks in Microsoft SQL Server

Locks in MS SQL Server are one way to ensure the integrity of data when changes are made by multiple users at the same time. MSSQL locks objects on a table when a transaction starts and releases the lock when the transaction ends. In this article, we will learn how to find locks in the MS SQL Server database and remove them.

It is possible to simulate a lock on one of the tables using an unfinished transaction (one that has not been completed by rollback or commit). For example, use the following SQL query:

USE tesdb1
BEGIN TRANSACTION
DELETE TOP(1) FROM tblStudents

Before any changes are made, SQL Server will lock the table. Now try opening SQL Server Management Studio and executing a simple SQL select query:

SELECT * FROM tblStudents

The query will freeze in the “Executing query” state until it timed out. This is because the SELECT query is trying to access data in a table that is locked by SQL Server.

Hang query in MS SQL due to table lock (block)

You can configure row-level or full-table-level locks in Microsoft SQL Server.

To get a list of all blocked queries in MSSQL Server, run the command

select cmd,* from sys.sysprocesses
where blocked > 0

You can also display a list of locks for a specific database:
SELECT * FROM master.dbo.sysprocesses
WHERE
dbid = DB_ID('testdb12') and blocked <> 0
order by blocked

The Blocked column shows the process ID of the process that blocked the resources. It also shows the wait time for this query (waittime in milliseconds). If required, this can be used to search for the earliest or latest locks.

find blocked processes in sql server

In some cases, a lock can be caused by an entire process tree. To find the source lock process, use the following query for SPID until you find the process with blocked=0, which is the initial process holding the lock.

select * FROM
master.dbo.sysprocesses
where 1=1
--and blocked <> 0
and spid = 59

The process SPID gives you the T-SQL code of the last SQL query executed by that process or transaction:

DBCC INPUTBUFFER(59)

dbcc get sql query string

To force kill the process and release the lock, run the command:

KILL number_of_session
GO

In my case, it will be:

KILL 59

kill process in mssql

You can create a separate stored procedure if locks are constantly occurring and you want to identify the most resource-intensive queries:

CREATE PROCEDURE GetCurrentQueryCode
@SPID int
AS
DECLARE @sql_handle binary(20), @stmt_start int, @stmt_end int
SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID AND ecid = 0
DECLARE @line nvarchar(4000)
SET @line = (SELECT SUBSTRING([text], COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end WHEN -1 THEN DATALENGTH([text]) ELSE (@stmt_end - @stmt_start) END) FROM ::fn_get_sql(@sql_handle))
print @line

Now, if you want to see the SQL query that locked the table, all you need to do is specify its SPID:

Exec GetCurrentQueryCode 51

SQL server stored procedure to find lock query

You can also get the query code from the sql_handle of the locking process. For example

select * from sys.dm_exec_sql_text (0x0100050069139B0650B35EA64702000000000000)

select * from sys.dm_exec_sql_text

You can use the Microsoft SQL Server Management Studio to search for locks in MS SQL Server. You can use one of the following methods:

  • Right-click on the server, open the Activity Monitor and expand Processes. You will see a list of requests waiting for resources to be released with a SUSPENDED status. MSSQL: Activity Monitor SUSPENDED processes
  • Select a database and navigate to Reports -> All Blocking Transactions. It also shows a list of locked queries and the SPID of the locking source. Microsoft SQL Server - All Blocking Transactions

1 comment
0
Facebook Twitter Google + Pinterest
previous post
Copy/Paste Not Working in Remote Desktop (RDP) Clipboard
next post
Internet Time Synchronization Failed on Windows

Related Reading

MS SQL Server Setup Stucks on Install/Uninstall

January 9, 2023

Configure SSL Connection Encryption in MS SQL Server

August 15, 2022

How to Reset SA Password on Microsoft SQL...

December 21, 2021

Upgrade Microsoft SQL Server Evaluation Edition to Standard/Enterprise

December 9, 2021

Configuring Always-On High Availability Groups on SQL Server

December 2, 2021

1 comment

kapsiR March 3, 2023 - 7:03 am

I can really recommend sp_whoisactive in this case:
https://github.com/amachanic/sp_whoisactive

It’s very helpful if you are looking for blocking queries and more!

Reply

Leave a Comment Cancel Reply

Categories

  • Active Directory
  • Group Policies
  • Exchange Server
  • Microsoft 365
  • Azure
  • Windows 11
  • Windows 10
  • Windows Server 2022
  • Windows Server 2019
  • Windows Server 2016
  • PowerShell
  • VMWare
  • Hyper-V
  • Linux
  • MS Office

Recent Posts

  • How to Run Program without Admin Privileges and Bypass UAC Prompt?

    March 24, 2023
  • Configure Network Settings on Windows with PowerShell: IP Address, DNS, Default Gateway, Static Routes

    March 24, 2023
  • Exchange Offline Address Book Not Updating in Outlook

    March 21, 2023
  • Attaching Host USB Devices to WSL or Hyper-V VM

    March 20, 2023
  • Sending an E-mail to a Microsoft Teams Channel

    March 17, 2023
  • How to Restore Deleted Users in Azure AD (Microsoft 365)?

    March 16, 2023
  • Fix: Remote Desktop Services Is Currently Busy

    March 15, 2023
  • Send-MailMessage: Sending E-mails with PowerShell

    March 14, 2023
  • Clear Cache and Temp Files in User Profiles on Windows (RDS) with PowerShell and GPO

    March 13, 2023
  • Prevent Users from Creating New Groups in Microsoft 365 (Teams/Outlook)

    March 6, 2023

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
  • Configure SSL Connection Encryption in MS SQL Server
  • MS SQL Server Setup Stucks on Install/Uninstall
Footer Logo

@2014 - 2023 - Windows OS Hub. All about operating systems for sysadmins


Back To Top