Blocking process in [sleeping] state with [SELECT] command

Question

Blocking process in [sleeping] state with [SELECT] command

Roust_m

SSCoach

Points: 17408
More actions
January 23, 2007 at 9:20 pm

#171991

Hi,
I had a chain of blocks on the server with a sleeping process having [SELECT] in [cmd] column in sysprocesses table. What could be the problem with such process? If it had [AWAITING COMMAND] instead of [SELECT], I could assume that the app waits for user input in the middle of a transaction, but what about [SELECT] as a command for blocking process in sleeping state?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

actually I'm not quite sure what you're asking - do you have a sleeping spid causing blocking or just a sleeping spid?

You can see if you have an open transaction ( if you're looking at sysprocesses I assume you understand what you're looking at? ).

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Tom Goltl SSCertifiable Points: 5755 More actions · Answer 2

What is the locking state in the sysprocesses table for that sid? An active process can go into a sleeping state if it is waiting for resources like disk.

Tom

Roust_m SSCoach Points: 17408 More actions · Answer 3

I have a process, which blocks other processes. If I do a query on this process from master..sysprocesses? I get this:

select spid,blocked,open_tran,status,cmd from sysprocesses (nolock) where spid = 66 or blocked<> 0

===========================================================================

spid, blocked, open_tran, status, cmd

66 0 1 sleeping SELECT

117 66 2 sleeping DELETE

73 117 2 sleeping DELETE

86 117 2 sleeping DELETE

87 117 2 sleeping DELETE

91 117 2 sleeping DELETE

99 117 1 sleeping SELECT

113 99 0 sleeping SELECT

130 99 2 sleeping ALTER TABLE

126 113 0 sleeping SELECT

128 113 0 sleeping SELECT

So I have a sleeping process causing blocking while not being blocked by any other process.

Roust_m SSCoach Points: 17408 More actions · Answer 4

BTW, it started this way:

time spid blocked open_tran status cmd

24/01/2007 13:07 66 0 1 sleeping SELECT

24/01/2007 13:07 117 66 2 sleeping DELETE

24/01/2007 13:08 66 0 1 sleeping SELECT

24/01/2007 13:08 117 66 2 sleeping DELETE

24/01/2007 13:09 66 0 1 sleeping SELECT

24/01/2007 13:09 117 66 2 sleeping DELETE

24/01/2007 13:09 73 117 2 sleeping DELETE

24/01/2007 13:09 86 117 2 sleeping DELETE

24/01/2007 13:09 87 117 2 sleeping DELETE

24/01/2007 13:09 91 117 2 sleeping DELETE

24/01/2007 13:09 99 117 1 sleeping SELECT

24/01/2007 13:09 113 99 0 sleeping SELECT

24/01/2007 13:09 130 99 2 sleeping ALTER TABLE

24/01/2007 13:09 126 113 0 sleeping SELECT

24/01/2007 13:09 128 113 0 sleeping SELECT

So I had process #66 in sleeping mode and on top of the block tree for three minutes. There was not any heavy disk activity at the moment and CPU was not much busy.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 5

it has an open tran, you need a commit.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Minaz Amin SSChampion Points: 11052 More actions · Answer 6

Hi,

This link will help you!

http://support.microsoft.com/kb/224453

Minaz Amin

"More Green More Oxygen !! Plant a tree today"