[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server 2005 locking problems

Posted on 2010-01-06
7
Medium Priority
?
151 Views
Last Modified: 2013-11-24
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?
0
Comment
Question by:espanolanthony
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26193325
may be those locks escalated by the time you ran those ..

0
 
LVL 1

Author Comment

by:espanolanthony
ID: 26193362
no i don't think so.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26193454
theblocked processes will be shown in the 'BlkBy' column when you run the sp_who2
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 43

Accepted Solution

by:
pcelba earned 1500 total points
ID: 26193464
You should also look at
select * from sys.dm_tran_locks;

or
select * from syslockinfo

on SQL 2000.
0
 
LVL 1

Author Comment

by:espanolanthony
ID: 26193549
so for
select * from sys.dm_tran_locks;

what columns to look at for locks or process that creates locking
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26193732
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
 
LVL 1

Author Closing Comment

by:espanolanthony
ID: 31673644
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Viewers will learn how the fundamental information of how to create a table.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question