Any Expected Changes to sp_who2 from the SQL Server Team?

  • Are there any changes, like column order changes, to be reasonably expected for sp_who2? I'm asking because I'm busy programming an application that needs to see who else is connected to a specific database and I'm using sp_who2 to see this.

    Maybe I should just EXEC sp_helptext 'sp_who2' and copy that myself... Opinions on this please?

    EDIT: Added a description to this thread to more accurately reflect my real need.

  • You should not rely on it. Use the dynamic management views instead, for example:

    sys.dm_exec_connections

    sys.dm_exec_sessions

    Paul

  • Thanks Paul. Which DMVs can I use that I can join with sys.dm_exec_connections abnd sys.dm_exec_sessions so that I can get a database name/id?

    Currently, my query is

    SELECT

    ec.session_id AS SessionID,

    ec.connect_time AS InitialConnectionTime,

    CASE WHEN (ec.last_read > ec.last_write) THEN ec.last_read

    ELSE ec.last_write

    END AS LastActionTime,

    --ec.last_read as LastReadOperation,

    --ec.last_write as LastWriteOperation,

    es.host_name AS HostName,

    ec.client_net_address AS ClientIPAddress,

    es.program_name AS SourceProgram,

    es.login_name AS LoginName,

    es.status AS Status--,

    ----xx.database_name Or DB_NAME(xx.databaseID) as DatabaseName

    FROM

    sys.dm_exec_connections AS ec INNER JOIN

    sys.dm_exec_sessions AS es ON ec.session_id = es.session_id

    Thanks once again for the helpful shove in the right direction.

    As an aside, is there a highlevel overview of the DMVs that you could recommend?

  • GDI Lord (3/11/2010)


    As an aside, is there a highlevel overview of the DMVs that you could recommend?

    Second question first:

    Dynamic Management Views and Functions (Transact-SQL)

  • GDI Lord (3/11/2010)


    Which DMVs can I use that I can join with sys.dm_exec_connections abnd sys.dm_exec_sessions so that I can get a database name/id?

    Database context only makes sense for a currently-executing statement, so you'll find database_id in the DMVs and DMFs that relate to an executing request.

    One possibly germane example is:

    sys.dm_exec_requests

  • Thanks Paul. I did some reading and SELECT * FROMing and saw that sys.dm_exec_requests gave me a database_id, but only for currently executing statements.

    I need to show that at 13:00:42 user Bob connected to database Documents and his last activity was at 16:20:02. I need this because in a certain section of one of our administration applications a user has the ability to rename a column on a table. We want to give the admin user the option of disconnecting everyone except himself when renaming the column, so we need to display this kind of information.

  • Try dbid on sys.sysprocesses - that's not a DMV/DMF but I can't remember the funky new equivalent for the moment...

  • After some more digging this morning, System Views leads me back to Compatibility Views again, which in turn leads me back again to sys.sysprocesses. I ran SELECT * FROM sys.sysprocesses with the Include Actual Exectution Plan option turned on to see how it gathered its information because, hey, it has to get it from somewhere. It uses the Table Value Function "SYSPROCESSES".

    USE master

    sp_helptext 'sp_who2'

    sp_helptext 'sysprocesses'

    Hint: Turn off the Include Actual Exectution Plan option - this greatly slows down the query as it has to graphically model what went on. Also, I've discovered that setting the results to Text instead of Grid helps 🙂

    This is the code for sys.sysprocesses:

    CREATE VIEW sys.sysprocesses AS

    SELECT

    spid,

    kpid,

    blocked,

    waittype,

    waittime,

    lastwaittype,

    waitresource,

    dbid,

    uid = convert(smallint, uid),

    cpu,

    physical_io,

    memusage,

    login_time,

    last_batch,

    ecid,

    open_tran,

    status,

    sid,

    hostname,

    program_name,

    hostprocess,

    cmd,

    nt_domain,

    nt_username,

    net_address,

    net_library,

    loginame,

    context_info,

    sql_handle,

    stmt_start,

    stmt_end,

    request_id

    FROM OpenRowSet(TABLE SYSPROCESSES)

    When I try and execute SELECT * FROM OpenRowSet(TABLE SYSPROCESSES) it gives me a syntax error. A quick peek at the OPENROWSET (Transact-SQL) Books On-Line entry reveals that it *is* a syntax error. Is the seemingly incorrect text outputted by sp_helptext on purpose? Similar story with from sys.sysprocesses_ex with (nolock) from sp_who2. How does the SQL Engine create these two object initially?

    I'm really trying to avoid having to use legacy code, so the search continues. Any more suggestions?

    Also, I'm renaming this thread to something that more accurately reflects my real need.

  • Some internal functions, like the one you mention, are only accessible from system stored procedures.

    There is a modern way to get the database id. Give me a minute...

  • Thanks Paul, I appreciate it. I'm still exploring on my side too.

  • http://connect.microsoft.com/SQLServer/feedback/details/257502/deprecation-of-sysprocesses-dmvs-doesnt-fully-replace-all-columns

    I am nevertheless sure I managed to avoid using sys.sysprocesses in the past - perhaps I am misremembering it.

    This isn't the method I was trying to think of, but for user databases, each session holds a shared database lock as long as it is connected. This is how SQL Server knows if anyone is using a user database.

    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';

    Paul

  • That works for me. Thank you very much Paul, I appreciate your help.

    For those of you who are interested in the SELECT statement to view connections and some connection information for USER DATABASES, here it is:

    SELECT

    ec.session_id AS SessionID,

    ec.connect_time AS InitialConnectionTime,

    CASE WHEN (ec.last_read > ec.last_write) THEN ec.last_read

    ELSE ec.last_write

    END AS LastActionTime,

    --ec.last_read as LastReadOperation, -- This is incorporated into LastActionTime, uncomment if needed

    --ec.last_write as LastWriteOperation, -- This is incorporated into LastActionTime, uncomment if needed

    es.host_name AS HostName,

    ec.client_net_address AS ClientIPAddress,

    es.program_name AS SourceProgram,

    es.login_name AS LoginName,

    es.status AS Status,

    resource_database_id AS DatabaseID,

    DB_NAME(tl.resource_database_id) AS DatabaseName

    FROM

    sys.dm_exec_connections AS ec INNER JOIN

    sys.dm_exec_sessions AS es ON ec.session_id = es.session_id INNER JOIN

    sys.dm_tran_locks AS tl ON es.session_id = tl.request_session_id

    WHERE

    tl.resource_type = N'DATABASE' AND

    tl.request_mode = N'S' AND

    tl.request_status = N'GRANT' AND

    tl.request_owner_type = N'SHARED_TRANSACTION_WORKSPACE';

  • Happy I could help!

  • Yeah, and I filed away some useful keywords that I found while spelunking through MSDN/BOL.

  • Thanks for the post. It helped me to get database_id. I was struggling for this ebcause neither sys.dm_exec_connection or sys.dm_exec_sessions had this id and I had to run a report against all seesions with open transactions.


    Kindest Regards,

    Ajay Prakash

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply