Search

Deprecation of sysprocesses - DMV's doesn't fully replace all columns by Tony Rogerson SQL

Active

102
0
Sign in
 to vote
Type: Bug
ID: 257502
Opened: 2/11/2007 10:18:28 AM
Access Restriction: Public
1
Workaround(s)
14
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.
Details (expand)
Product Language
English

Version

SQL Server 2005 SP2 - Developer Edition

Category

SQL Engine

Operating System

Windows Vista
Operating System Language
English
Steps to Reproduce
Open a connection to the database, select a user database, use PRINT @@SPID to determine your session_id.

In another connection..

select *
from sys.dm_exec_requests
where session_id = <spid from previous>

Actual Results
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.
Expected Results
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.


Platform

X64
File Attachments
0 attachments
Sign in to post a comment.
Posted by nealgseattle 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.
Posted by brianpaulflynn 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.
Posted by rwanauo 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"

http://connect.microsoft.com/SQLServer/feedback/details/337379/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.
Posted by aaronbertrand 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?
Posted by MrTS on 7/13/2011 at 3:54 AM
database_id is still missing in SQL 2008/R2.
I really need it. :-(
Posted by brimhj on 4/6/2010 at 5:32 PM
I forgot to add that I am using SQL 2008 SP1 as well.
Posted by brimhj 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.

Posted by Kalen Delaney 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
Posted by 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.
Posted by Pawel Potasinski on 8/7/2009 at 4:24 AM
Please, reopen this item. It's not fixed and can't be ignored just like that.
Posted by Dan W 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.
Posted by aaronbertrand 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).
Posted by way0utwest 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.
Posted by Microsoft on 11/19/2007 at 1:57 PM
Hi Tony,

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.

Thanks,
-Vineet Rao
Posted by 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.

Thanks,
Tony.
Posted by Microsoft on 2/12/2007 at 11:19 AM
Hi,

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.

Regards,
Tomer Verona
SQL Server Development
Sign in to post a workaround.
Posted by Paul White NZ on 3/12/2010 at 12:38 AM
-- Shows user databases 'in use' by a session
SELECT request_session_id,
        resource_database_id
FROM    sys.dm_tran_locks
WHERE resource_type = N'DATABASE'
AND     request_mode = N'S'
AND     request_status = N'GRANT'
AND     request_owner_type = N'SHARED_TRANSACTION_WORKSPACE';
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement