Locating idle sessions using dm_exec_... DMV''s

Hi

I have been looking at the new DMV's prefixed with dm_exec_....and found a limitation with them.

Books online says sysprocesses is replaced with sys.dm_exec_connections, sys.dm_exec_requests and sys.dm_exec_sessions. The problem I came accross is identifying any sessions connected to a specific database which were idle. This is the sort of thing you need to know if you tried to restore a database and it says the database is in use.

I wonder if this is a bug or by design?

you can find idle connections by determining which connections have no active requests.

select *

from sys.dm_exec_connections ec

left join sys.dm_exec_requests er

on ec.connection_id = er.connection_id

where er.connection_id is null

sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests will actually give you a super-set of what sysprocesses used to. A bit more work to pull out some of the information we all know and love, but much more powerful.

-Jerome

|||

Jerome

Thanks for the reply but it still does not work for me. If I run:

select spid,db_name(dbid) from sysprocesses where spid>50

I get

spid

--

51 msdb

52 master

53 master

however If I run

select ec.session_id,db_name(database_id)

from sys.dm_exec_connections ec

left join sys.dm_exec_requests er

on ec.connection_id = er.connection_id

I get

session_id

-- --

51 NULL

52 NULL

53 master

|||

Sorry Steve,

missed your point about getting the dbname that the session had last used. One way is to add sys.dm_tran_locks and get the database that the session has a lock on

select db_name(isnull(resource_database_id,1)), ec.session_id

from sys.dm_exec_connections ec

left join sys.dm_exec_requests er

on ec.connection_id = er.connection_id

left join sys.dm_tran_locks tl on tl.request_session_id = ec.session_id and resource_type='DATABASE'

where er.connection_id is null

-Jerome

|||Thanks Jerome, that returned the answer I was looking for.|||

Hi

Please help me with this:

I am looking for the Login Name, Hostname, cpu time and the text( sql stmts executed)

Using the DMVs

I used this query:

select * from (

SELECT sys.dm_exec_sessions.login_time,

sys.dm_exec_sessions.host_name,

sys.dm_exec_sessions.program_name,

sys.dm_exec_sessions.login_name,

sys.dm_exec_sessions.nt_user_name,

sys.dm_exec_query_stats.total_worker_time,

sys.dm_exec_requests.plan_handle

FROM sys.dm_exec_sessions

INNER JOIN sys.dm_exec_requests

ON sys.dm_exec_sessions.session_id = sys.dm_exec_requests.session_id

INNER JOIN sys.dm_exec_query_stats

ON sys.dm_exec_requests.sql_handle = sys.dm_exec_query_stats.sql_handle

)temp cross apply sys.dm_exec_sql_text(plan_handle) as sql_text

but its giving me only the text for that session of the user(not all the text), actually i am looking for all the text( stmt executed) by the user, cpu time since its in the cache/since user logged in

i get all the text taking long time to run with this without userid:

select * from (

select TOP 1000

sum(qs.total_worker_time) as total_cpu_time,

sum(qs.execution_count) as cnt,

count(*) as number_of_statements,

qs.plan_handle

from

sys.dm_exec_query_stats qs

group by qs.plan_handle

order by sum(qs.total_worker_time) desc

) tmp

cross apply sys.dm_exec_sql_text(plan_handle) as sql_text

please help me

thanks