Guangning Yu's Blog

SQL Server Queries

2020-11-18 14:26:27  |  sql_server

Check currently running queries

reference: https://stackoverflow.com/a/29400789

  1. SELECT SPID = er.session_id
  2. ,STATUS = ses.STATUS
  3. ,[Login] = ses.login_name
  4. ,Host = ses.host_name
  5. ,BlkBy = er.blocking_session_id
  6. ,DBName = DB_Name(er.database_id)
  7. ,CommandType = er.command
  8. ,ObjectName = OBJECT_NAME(st.objectid)
  9. ,CPUTime = er.cpu_time
  10. ,StartTime = er.start_time
  11. ,TimeElapsed = CAST(GETDATE() - er.start_time AS TIME)
  12. ,SQLStatement = st.text
  13. FROM sys.dm_exec_requests er
  14. OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
  15. LEFT JOIN sys.dm_exec_sessions ses
  16. ON ses.session_id = er.session_id
  17. LEFT JOIN sys.dm_exec_connections con
  18. ON con.session_id = ses.session_id
  19. WHERE st.text IS NOT NULL

Check currently running jobs

reference: https://stackoverflow.com/a/54684796

  1. WITH
  2. CTE_Sysession (AgentStartDate)
  3. AS