Utility Queries 01

/*

Idle process with open transaction
*/

select spid,kpid,blocked,status,cmd,open_tran,dbid,
datediff(s,last_batch,getdate()) as idle_secs,waittype,lastwaittype,waitresource,
login_time,last_batch,cpu,physical_io,memusage
from sys.sysprocesses
where status = 'sleeping' and open_tran > 0
---------------------------------------------------------------------------------------------------
 
/*

Transaction Running in the current session
*/
select * fromsys.dm_tran_current_transaction
 
---------------------------------------------------------------------------------------------------

--Number of threads used


select count(*) as 'Number of threads'

from sys.dm_os_waiting_tasks
where wait_type <> 'THREADPOOL';
go
---------------------------------------------------------------------------------------------------
 /*


Check whether a DAC is in use by running
the following query. If there is an active DAC,
the query will return the server process id (spid)
for the DAC; otherwise, it will return no rows.
*/

select t2.session_id
from sys.tcp_endpoints as t1
join sys.dm_exec_sessions as t2
on t1.endpoint_id = t2.endpoint_id
where t1.name='Dedicated Admin Connection';
GO

-------------------------------------------------------------------------
/*
protocol used for the current connection:
*/
select net_transport
from sys.dm_exec_connections
where session_id = @@spid;
GO

-------------------------------------------------------------------------