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

SQL Pivot

Pivot function can be used to convert rows in to columns in a data set. Following is an example that you may want to use this functionality. You have two tables.

tblContact

ContactID	ContactMethod	ContacText	PersonID
1	Email	wijitha@gmail.com	1
2	MobilePhone	07724579564	2
3	HomePhone	01754546566	3
4	Email	user@gmail.cpm	4
5	MobilePhone	01245655454	5
6	Email	user2@gmail.com	6

tblPerson

PersonID	FirstName	LastName
1	wijitha	wijenayake
2	Sandun	perera
3	chanaka	silva

You want to show each person with their contact methods in a Report or a Grid view. The normal SQL query that you write may looks like this.

select * from tblperson

inner join tblContact on tblperson.PersonID = tblContact.PersonID

Then the resultant data set will be like this.

Person ID	Contact ID	ContactMethod		ContactText
1	1		Email	wijitha@gmail.com
1	2		MobilePhone	07724579564
1	3		HomePhone	01754546566
2	4		Email	user@gmail.cpm
2	5		MobilePhone	01245655454
3	6		Email	user2@gmail.com

But now, it is very difficult to display each person’s records in a same row. It will be easy if data set returns “ContactMethod” types as columns. Here you can get use of PIVOT statement. Now your new query will be like this.

select tblperson.*, tblContact.ContactMethod, tblContact.ContactText into #temp from tblperson

inner join tblContact tblperson.PersonID = tblContact.PersonID

select * from #temp

pivot

(

max(ContactText)

FOR ContactMethod in ([Email], [MobilePhone], [HomePhone])

) AS P

drop table #temp

Resultant data set will be like this

PersonID	Emai	MobilePhone	HomePhone
1	wijitha@gmail.com	07724579564	01754546566
2	user@gmail.cpm	01245655454	NULL
3	user2@gmail.com	NULL	NULL

Now it is easy to display this data on a Report or a Grid view.

Syntax:

• SELECT columns
• FROM table
• PIVOT
  (
  Aggregate Function(Measure Column)
  FOR Pivot_Column IN ([Pivot Column Values])
  ) AS Alias

Note: Values (Email, HomePhone …) of pivot column (ContactMethod) should be a single word values.