If you are regularly using a check on the sysprocesses table to see what is slowing down your sql server, looking for that usual culprit in middle management who absolutely must have access to the database despite the fact that he/she has no idea about what they are doing to the performance, then heres a sql 2005+ alternative to using sysprocesses.
The dynamic management views offer access to much more information that the catalog views used to uery the server compatible with the old methods. The following script does not offer much more but it can be imporved and expanded upon to provide more in if you need it. Just check out the three views referenced in the query. I have purposefully kept this simple to keep it similar to sysprocesses, as a building block for development.
You probably were doing something like this in 2000 (perhaps with a few different columns or just an asterisk)…
try this instead
, DB_NAME(R.database_id) as database_name
FROM sys.dm_exec_requests R
INNER JOIN sys.dm_exec_sessions S on R.session_id = S.session_id
INNER JOIN sys.dm_exec_connections C on R.connection_id = C.connection_id
LEFT JOIN sys.dm_exec_sessions BS on R.blocking_session_id = BS.session_id
Note that in this statement I have used an inner join to the connection view so that you only see sessions with connections. This filters out the internal sql operations.