In SQL Server 2005 you see a process(SPID 623) that takes a lot of time and is locking other processes. So you give it a KILL 623 command, but nothing is happening. In the log you see “Process id 623 killed by Hostname hendrik” After this you do a select on the sysprocesses table “SELECT * FROM sysprocesses WHERE SPID = 623” And the waittype is 0x0800 and lastwaittype “NETWORKIO”
Why is the process not KILLED?
If the client does not fetch all result rows, the server will eventually be forced to wait when writing to the client. This is seen as a sysprocesses.waittype of 0x0800. While waiting on the network, no SQL Server code is being run that can interrogate the PSS and detect a KILL command. If the spid holds locks prior to waiting on the network I/O, it may block other processes.