SQL Server - what is each session doing? running? sleeping?

SELECT s.status, s.session_id, '################', * FROM sys.dm_exec_sessions s ORDER BY s.status;

The current Session is:

SELECT @@SPID;

It will always be running, or queries wont work.

SQL Server - Show open transactions

SELECT * FROM sys.dm_tran_session_transactions;

SQL Server - Is there an open transaction on a session_id?

-- Is there an open transaction on a session_idSELECT  st.transaction_id,
  at.name,
  at.transaction_begin_time,
  at.transaction_state,
  at.transaction_status
FROM    sys.dm_tran_session_transactions st
  JOIN    sys.dm_tran_active_transactions at    ON st.transaction_id = at.transaction_id
WHERE   st.session_id = 52;--your blocking spid

SQL Server - What was the last statement on a session_id

-- What was the statement on a session_idSELECT  c.session_id, t.text,
  QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid)) + '.'  + QUOTENAME(OBJECT_NAME(t.objectid, t.dbid)) proc_name,
  c.connect_time,
  s.last_request_start_time,
  s.last_request_end_time,
  s.status
FROM    sys.dm_exec_connections c
  JOIN    sys.dm_exec_sessions s    ON c.session_id = s.session_id
  CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE   c.session_id = 52;--your blocking spid

SQL Server find out what is blocking what, or find a dead lock

Use this query, but you must be serveradmin to run it:

SELECT  db.name DBName,
  tl.request_session_id,
  wt.blocking_session_id,
  OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
  tl.resource_type,
  h1.TEXT AS RequestingText,
  h2.TEXT AS BlockingTest,
  tl.request_mode
FROM sys.dm_tran_locks AS tl
  INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
  INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
  INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
  INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
  INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
  CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
  CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2;