Monitor table locks in SQL server database

At any given time, tables in a database can be locked because of several reasons. Most probably this can be happen,

• Not commiting a transaction after insert or update query
• Not roll backing a transactions after encounting  exceptions

When there are table locks in the DB, these tables can not be accessed from any other transaction. So that the query execution on these tables will be blocked. Hence some functionalities in your system might be stuck until you restart database or your application.
In your system you might experience some functionalities are been stuck only in some times. Since it is happening randomly it can not be a problem in your code. And its really hard to figure out them. There is a possibility that this is happening because of table locks. To make sure that, we have to identify which tables are been locked at a given time. Following queries are help you for this.


--Querry for display locked tables in the given database
select db.name as DBName,
    db.dbid as DBID,
    sysobjects.name as 'LockedObjects',
    locks.request_Type,
    locks.Request_status,
    locks.request_owner_type
from sys.dm_tran_locks as locks
inner join sysobjects on sysobjects.id = locks.resource_associated_entity_id
inner join master..sysdatabases db on db.dbid = locks.resource_database_id
where db.name = 'corpdb' --Put you database name here

--Querry for display blocked uses calls in the given database
select cmd as command,
    process.waittime,
    process.hostname,
    process.program_name,
    process.loginame as LoginName,
    process.status
from sys.sysprocesses process
inner join master..sysdatabases db on db.dbid = process.dbid
where blocked > 0
and db.name = 'corpdb' --Put you database name here