Useful code for tracking spid activities

select spid,blocked,waittime,lastwaittype,waitresource,db_name(dbid)as 'Database',
user_name(uid)as 'User role',cpu,physical_io,memusage,hostname,nt_username,net_library
from master.dbo.sysprocesses
order by waittime desc

This code gives a lot of useful information when tracking down problems inside sql server. It gives waittypes and times for every spid currently active in the server.