SP_Lock Help
Jul 20, 2005
I am wanting to learn how to diagnose locking problems on a query by
looking at locking data in SP_Lock and not SQL Profiler. Books on line
describes what each column means, but I would like an example to know
what actions to take if I find 2 SPIDs competing for resources. For
example, should I kill one or the other or do something else altogether.
Help appreciated. Thanks.
Frank
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
View 2 Replies
Oct 5, 2000
Ive got a tricky deadlock problem and i need more info than sp_lock or trace flags 1024/1025 are providing.
sp_lock provides something like the following for KEY locks ...
objid indid type resource mode status
----------- ----------- ---- --------------- ---------- ------
1323867783 10 KEY (c2a64d055f97) X GRANT
I need to know specifically what the 'resource' is ... ie the index key or key range or table row it applies to ...
Anyone know some sneaky undocumented feature to do this ?
Duncan
View 1 Replies
View Related
Mar 16, 2004
Is there any way to write a line to a table showing who had a lock at a particular time?
We have a need to rollback transactions that persist for too long and cause application errors. what i want to do is to dump the details of the connection into a table for analysis later.
i am thinking along the lines of
SELECT loginname
FROM sp_who
WHERE spid in
(
SELECT spid
FROM sp_lock
)
or something!!!!
any ideas anyone?
View 3 Replies
View Related
Dec 13, 2005
Hi!
I need some examples of SP_Lock & SP_Who.
Best Regards,
Shabber Abbas Rizvi.
View 1 Replies
View Related
Jul 20, 2005
On a hung query and using the SP_Lock to reveal 2 contending SPID's:Is there ever a time when you would justify the release one SPID insteadof both SPIDs that were contending for the same ObjID? would this dependon the modes the SPIDs hold? Help appreciated.Frank*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
