What version of SQL are you running? I think for the dynamic management views (the ones that start with dm_) you have to be running at least 2008. But the code below is what you need.
My performance tab has been 0's since I upgraded to 7.32, it worked it 7.25. But the logging in the embeds in the MultiHost demo app was more helpful to me than the performance tab.
CREATE View [dbo].[dv_ActiveTransactions]
as
SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
dtat.transaction_begin_time AS [Begin Time],
[s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
[s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
[s_est].text AS [Last T-SQL Text],
[s_eqp].[query_plan] AS [Last Plan],
s_er.blocking_session_id
FROM
sys.dm_tran_database_transactions [s_tdt]
JOIN
sys.dm_tran_session_transactions [s_tst]
ON
[s_tst].[transaction_id] = [s_tdt].[transaction_id]
Join sys.dm_tran_active_transactions DTAT
on s_tdt.transaction_id = dtat.transaction_id
JOIN
sys.[dm_exec_sessions] [s_es]
ON
[s_es].[session_id] = [s_tst].[session_id]
JOIN
sys.dm_exec_connections [s_ec]
ON
[s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
sys.dm_exec_requests [s_er]
ON
[s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
Where DATEDIFF(mi,dtat.transaction_begin_time,GETDATE())>=2
GO
Create View [dbo].[dv_CursorSessions]
as
select login_time,status,last_request_start_time,last_request_end_time,
ses.transaction_isolation_level, ses.lock_timeout,row_count, c.*, t.text
from sys.dm_exec_sessions ses
cross apply sys.dm_exec_cursors(ses.session_id) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t
where ses.is_user_process=1 and ses.session_id <> @@SPID
GO
Topic: SQL Sleeping Connections Part 2 (Read 2654 times)