January 15, 2008 at 3:35 am
Hello
I have run 'select * from sysprocesses' or 'sp_who'
and 'sp_lock'
so, in thaose i want to checkout BLOCK and WAITING or Deadlock..
So, which parameter(value) i have to see those queries to check out BLOCK and WAITING ?
and when i have to KILL Processses...?
Please give me hint to checkout BLOCK and WAITING and when KILL...?
January 15, 2008 at 3:43 am
I'm not sure i understand what you're after.
If you query sysprocesses (select * from master..sysprocesses where spid > 50) you'll see there are four columns - blocked, waittime, lastwaittype and waitresource.
If blocked is not 0, then it indicates that the connection is blocked. The number in the blocked column is the spid of the blocking connection.
waitresource and lastwaittype show you what the request is waiting for (it might not be blocked)
waitresource, along with lastwaittype, shows what it's waiting for and the waittime shows you how long (in ms) it has been waiting.
As for kill, you shouldn't kill connections without a good reason. Waits are normal and most blocking will resove itself in a very short amount of time.
If you have serious blocking problems, it's better to find out why there's so much blocking and resolve the cause
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2008 at 3:44 am
Hope somebody can come up with better anser.
in sysprocess there is a column blocked which shows the processid of the blocking process.
What about using profiler for this.
"Keep Trying"
January 15, 2008 at 3:54 am
Chirag (1/15/2008)
Hope somebody can come up with better anser.
Was my answer inadequate?
in sysprocess there is a column blocked which shows the processid of the blocking process.
What about using profiler for this.
For what?
Profiler doesn't have any events that fire when one process blocks another. It has numerous events for requesting, acquiring, cancelling and releasng locks.
It's got an event that fires when a process has been blocked for a certain amount of time, that's all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2008 at 4:23 am
Hi Gail
I meant my answer was not fully correct. Any way i had taken this topic in my browser before you had answered, but updated my answer after you had updated yours .
"Keep Trying"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply