Kill Sessions – Life saving script

Some of you have come across hard times when database keep not responding when doing SQL server restart, while alter database settings, while changing Single/Multi user settings…. etc

This script can be used to find blocking sessions.

Once blocking session is identified, analyse carefully and KILL the particular session id.

This small script saved my life many times,so its here for you… Cheers!!

select
 l.resource_type,
 l.request_mode,
 l.request_status,
 l.request_session_id,
 r.command,
 r.status,
 r.blocking_session_id,
 r.wait_type,
 r.wait_time,
 r.wait_resource,
 request_sql_text = st.text,
 s.program_name,
 most_recent_sql_text = stc.text
 from sys.dm_tran_locks l
 left join sys.dm_exec_requests r
 on l.request_session_id = r.session_id
 left join sys.dm_exec_sessions s
 on l.request_session_id = s.session_id
 left join sys.dm_exec_connections c
 on s.session_id = c.session_id
 outer apply sys.dm_exec_sql_text(r.sql_handle) st
 outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) stc
 where l.resource_database_id = db_id('DatabaseName')
 order by request_session_id;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s