March 11, 2010 at 5:32 am
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.
March 11, 2010 at 5:52 am
You should not rely on it. Use the dynamic management views instead, for example:
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 6:46 am
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?
March 11, 2010 at 6:51 am
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)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 6:54 am
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:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 7:17 am
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.
March 11, 2010 at 8:37 am
Try dbid on sys.sysprocesses - that's not a DMV/DMF but I can't remember the funky new equivalent for the moment...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 12:49 am
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.
March 12, 2010 at 1:08 am
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...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 1:31 am
Thanks Paul, I appreciate it. I'm still exploring on my side too.
March 12, 2010 at 1:36 am
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
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 2:12 am
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';
March 12, 2010 at 2:26 am
Happy I could help!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 2:32 am
Yeah, and I filed away some useful keywords that I found while spelunking through MSDN/BOL.
February 4, 2011 at 8:33 am
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.
Ajay Prakash
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply