Find Locking SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Find Locking SP

Author

Topic

afrugone
Starting Member

3 Posts

Posted - 2005-12-30 : 13:21:28

I've have a lot of locks in a SQL Server, and I'd like to identify which SPs are locking what tables, I've being trying with sp_who, sp_who2 and sp_lock, so i can identify the process number, but I don't have any idea what this process is doing (which sp is running? and what command?) and which table is locked by this process, can anybody send me some querys to get this information

I'm novice in SQL Server, and I've not access to the Enterprice manager console, and I've only have priveleges to read data from the database

Thanks for your help
Alfredo

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-12-30 : 14:10:56

If you can identify the process number <spid>, you can use DBCC INPUTBUFFER(<spid>) to see what statement was issued for that spid.

rockmoose

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-12-30 : 14:38:07

Hi Alfredo,

Welcome to SQLteam.com. You can also use sql profiler to check which all stored procedures are slow and resource consuming. You can filter them with events like locks, blockage etc.

Check Books online for more information.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

afrugone
Starting Member

3 Posts

Posted - 2006-01-02 : 10:27:17

quote:
Originally posted by rockmoose

If you can identify the process number <spid>, you can use DBCC INPUTBUFFER(<spid>) to see what statement was issued for that spid.

rockmoose

Rockmoose many thanks for your help, I tried DBCC INPUTBUFFER(<spid>), but I don't have permission.

Regards

Alfredo

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-01-02 : 11:18:39

I haven't checked this myself, but the documentation says that you should be able to see the processes that you own.
So if you run sp_who (or sp_who2), and the loginnname column displays your loginnname, I reckon you should be able to use
dbcc inputbuffer on that process id (spid) ?

rockmoose

afrugone
Starting Member

3 Posts

Posted - 2006-01-02 : 12:32:05

So I think this the problem, I don't own these process, I'm generating some reports from the database, and there is many other process from other peopple over the same database, I'm asking to the DBA what applications are generating all that locks, but they don't good control of the database, so I'm trying to get taht information myself, but as I told I only have read access to the server.

Regards

Alfredo

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-01-02 : 13:56:11

Are these locks problematic (i.e. blocking?) or do they just "seem out of place"?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-01-02 : 15:37:36

Can the reports run under a lower transaction isolation level?
read uncommitted / or use with(nolock) hint in the queries.
(that is probably the quickest "fix")

Tell the dba that you need help/support to resolve this problem.
Here is one option to monitor locking: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49847
Ask them if they can run this (with elevated privileges) when you experience the locking problems: http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html

Also what blindman says, is the locking "bad"?
A certain amount of locking is normal...
Have you looked at how optimized your queries are, and examined the execution plans?

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources