How to find queries creating lock in Microsoft SQL Server Database ?
1. Take connection to database and use below query
USE Master
GO
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0
GO
2. With above query you would get session ID , Wait duration and blocking session id.
Use below query to fetch what query is creating problem
Use below query to fetch what query is creating problem
DBCC inputbuffer (Session_ID)
3. With above you should be able to get the exact query causing the problem, now as a DBA you can report this to your development team and if required KILL it using below query
