SQL Server 2005 locking problems

Can some one tell me how can i see what process are blocking or creating locks on a database.

i used sp_who2
 and also
select * from sys.sysprocesses where blocked > 0

but could not find what processes creates locking or blocking. any focus on this friends?
LVL 1
espanolanthonyAsked:
Who is Participating?
 
pcelbaConnect With a Mentor Commented:
You should also look at
select * from sys.dm_tran_locks;

or
select * from syslockinfo

on SQL 2000.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
may be those locks escalated by the time you ran those ..

0
 
espanolanthonyAuthor Commented:
no i don't think so.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Aneesh RetnakaranDatabase AdministratorCommented:
theblocked processes will be shown in the 'BlkBy' column when you run the sp_who2
0
 
espanolanthonyAuthor Commented:
so for
select * from sys.dm_tran_locks;

what columns to look at for locks or process that creates locking
0
 
pcelbaCommented:
Almost all columns are important (but some of them are constants). The process ID is in request_session_id.

See more info at  http://msdn.microsoft.com/en-us/library/ms190345.aspx
0
 
espanolanthonyAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.