SQL Server Home
Deprecation of sysprocesses - DMV's doesn't fully replace all columns
Tony Rogerson SQL
2/11/2007 10:18:28 AM
User(s) can reproduce this bug
According to BOL sysprocesses system view is on its way out of the product.
The column (there may be others) dbid in sysprocesses can not be determined through the execution DMV's.
sys.dm_exec_requests only shows currently executing sessions, so if a connection is inactive but still has context within a database there is no reliable way of determining this without the use of sysprocesses.
SQL Server 2005 SP2 - Developer Edition
Operating System Language
Steps to Reproduce
Open a connection to the database, select a user database, use PRINT @@SPID to determine your session_id.
In another connection..
where session_id = <spid from previous>
No row appears; I am unable to determine what users have their context set to that database which prevents me from system management tasks like restoring databases.
New column on the connection DMV 'database_id' which shows the current context the user connection is in; for executing SQL we can determine the currently 'executing' context by querying sys.dm_exec_requests.
to post a comment.
Please enter a comment.
on 5/14/2014 at 5:11 AM
I do not want to use backward compatibility views, instead I use DMVs. But for the query below it is not possible to rewrite it with DMVs. Is there any workaround for that?
select * from sys.sysprocesses where dbid in (db_id('dbname1'), db_id('dbname2'))
By the way it has been 7 years since this issue is posted first.
SQL Server DBA
on 3/5/2013 at 8:46 AM
I have a workaround for the database id, but I'm seconding Kalen's comment about not having another way to determine nested transactions, especially for sessions that are not active. This is a must-have.
on 7/25/2012 at 6:12 PM
RE : Our goal is to ensure that the replacement covers all necessary functionality before deprecating a feature.
I have discovered that dm_exec_sessions doesn't update/refresh it's self in the same way as sysprocesses. Specifically, it does not update metrics e.g. cpu, reads & writes as sysprocesses does. If you are monitoring a running thread, it won't update values for that thread until the thread completes. I am creating a presentaiton that will demonstrate this point which will be delivered at #sqlsat154. If I am wrong i.e. missing a detail that would help, please let me know, otherwise, I still see value in sysprocesses over dm_exec_sessoins.
on 11/22/2011 at 7:22 AM
From another Connect posted issue that will not be getting fixed...
"Open and Active Connection Dissappears Momentarily from SYSPROCESSES"
if you join on sysprocesses, you still can't be sure you will get the database id.
on 7/18/2011 at 7:05 AM
This is fixed in Denali CTP3 (at least the part about database_id - this column has been added to sys.dm_exec_sessions). For anyone who says they "really need it" why is sys.sysprocesses insufficient for now?
on 7/13/2011 at 3:54 AM
database_id is still missing in SQL 2008/R2.
I really need it. :-(
Jason Brimhall (MCM)
on 4/6/2010 at 5:32 PM
I forgot to add that I am using SQL 2008 SP1 as well.
Jason Brimhall (MCM)
on 4/6/2010 at 5:27 PM
I still don't see this as having been resolved. I think this is an important thing to fix prior to the deprecation of sys.sysprocesses.
on 8/7/2009 at 11:07 AM
As Aaron mentioned, another column that must be available is open_tran. We have an open_tran in dm_exec_requests, but that is only avaialble for active sessions. A much bigger problem is when an inactive session is holding a transaction open, and right now, the only way to see the depth of any transaction nesting for inactive sessions is to use sysprocesses. Do NOT remove sysprocesses (or close this request) until we have another way to get open_tran count
Tony Rogerson SQL
on 8/7/2009 at 4:44 AM
Needs re-opening please; we at least need to be reassured that sysprocesses will not be removed without ALL the existing columns being replicated in the DMV's and in what build this will happen.
Pawel Potasinski, MSFT
on 8/7/2009 at 4:24 AM
Please, reopen this item. It's not fixed and can't be ignored just like that.
on 8/6/2009 at 9:32 PM
I still don't see this in SQL Server 2008 Standard SP1 (10.0.2531), so where was this supposedly fixed? I would expect this in either sys.dm_exec_connections or sys.dm_exec_sessions, but I don't see database_id or anything specific to a database in either of these views.
I agree with Tony that the important thing is for it to be there before sysprocesses is removed, but it's frustrating to have it be more than a year later apparently without an available fix.
on 7/1/2008 at 1:10 PM
Agreed! You can't mark it as fixed if we are not even going to see a change until SQL 2011. It certainly isn't fixed in RC0 (and another thing that Kalen points out is missing is open tran count).
on 2/4/2008 at 9:30 AM
Please let us know if this is to be included in Katmai (CTP6/7) or reopen the issue until a timeline is established.
on 11/19/2007 at 1:57 PM
Our goal is to ensure that the replacement covers all necessary functionality before deprecating a feature. In this particular instance, we missed it and we will add it in a future release of SQL Server.
Tony Rogerson SQL
on 2/12/2007 at 9:31 PM
So long as the functionality of sysprocesses can be fully reproduced using DMV's before it is deprecated from the product then I don't see a problem.
on 2/12/2007 at 11:19 AM
Thanks for raising this issue. We are aware of this issue and will be fixing it in future releases of SQL Server. Unfortunately, at this point the fix cannot make it in for SP2.
SQL Server Development
to post a workaround.
Please enter a workaround.
Paul White NZ
on 3/12/2010 at 12:38 AM
-- Shows user databases 'in use' by a session
WHERE resource_type = N'DATABASE'
AND request_mode = N'S'
AND request_status = N'GRANT'
AND request_owner_type = N'SHARED_TRANSACTION_WORKSPACE';
© 2014 Microsoft