Posted By

marcocs on 09/20/09


Tagged

performance admin Mantenimiento


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Tyster


Who block Who?


 / Published in: SQL
 

URL: http://www.microsoft.com/learning/en/us/book.aspx?ID=8565&locale=en-us

"LEFT JOIN sys.dmexecsessions SB" only for test... It is not necesary becouse in a real block condition "blockingsessionid" is not null. so, when you have tested it just replace Left with INNER JOIN sys.dmexecsess...

Good block detection to all!

  1. SELECT D.name ,
  2. R.Session_ID ,
  3. S.login_name ,
  4. S.nt_user_name ,
  5. R.Start_time ,
  6. R.STATUS ,
  7. R.Command ,
  8. R.blocking_session_id ,
  9. SB.login_name blocking_login_name ,
  10. T.text
  11. FROM sys.dm_exec_requests R
  12. CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T
  13. INNER JOIN sys.DATABASES D ON R.database_id = D.database_id
  14. INNER JOIN sys.dm_exec_sessions S ON R.session_id = S.session_id
  15. LEFT JOIN sys.dm_exec_sessions SB ON R.blocking_session_id = SB.session_id
  16. WHERE R.sql_handle IS NOT NULL

Report this snippet  

You need to login to post a comment.