Handling with currently running and blocking sessions

info-59812, 2016-10-05 (first published: 2016-09-16)

Getting probelm creating sessions or queries is a routine job for a DBA , Here is more efficient way of resolving this issue without using any third party tool.

For demo purpose, we are creating our own table and inserting some data, and then we will create different sessions , and then blocking sessions . All code is in code section here i'll use image of commands and results for better understanding.

Create Employee table

Insert dummy data in table

Now our dummy table Employee has 12 rows, open another query in SSMS (which will create another session)

Write some insert command in new query window

here you can see we start transaction with BEGIN TRAN keyword but without END TRAN, so it makes transaction state open

now run script to see currently open running session which are in open state,

here you can see its showing two sessions 54 and 53 one is where we are running the query and other one is which is in open state, the session where we issue command but did not make it complete. DBA can contact the session owner to complete that transaction or can take any other transaction.

Now lets create another session which is updating the record which is not commited , i mean the open session query.

so in new query window we wite statement and execute

here you can see system is in running state without completing the statement.

now come to any other query window and run the query below to check whcih are blocking sessions

Result is showing that blocked session is 58 in which we use update query and blocking session is 54 in which we insert some record which is not committed.

now we can clear blocked session by committing 54 session or any other option.

/****Creating dummy table Employee ****/CREATE TABLE Employee ( Empid int NOT NULL, Name nchar(10) NULL, City nchar(10) NULL ) ON [PRIMARY] GO /**** Insert dummy data in Employee table *****/Insert into Employee Values(1245,'George','Jax'), (1045,'Peter','Anadale'), (1157,'John','Dallas'), (1175,'Pete','Topeka'), (875,'Petron','Vienna'), (2311,'Kohli','Mumbai'), (1547,'Peter','Kansas'), (3514,'Abian','KHI'), (4251,'Ghani','Alexandria'), (957,'Ahmed','Vienna'), (1084,'Bhanu','Manderin'), (2954,'Ganeshan','Mcclean') /***** Insert query in new session ****/BEGIN TRAN Insert into Employee Values(1245,'George','Jax') /**** Query to check currently running sessions ****/SELECT DISTINCT name AS database_name, session_id, host_name, login_time, login_name, reads, writes FROM sys.dm_exec_sessions LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id WHERE resource_type <> 'DATABASE' --AND name ='specific db name' ORDER BY name /**** update query in new session ****/update Employee set name = 'SHERAZ' where empid = 1245 /**** Query to check blocking queries with session id ****/SELECT session_id, blocking_session_id, text FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle); /*** Command if you want to kill blocking session ****/kill (54)

Handling with currently running and blocking sessions

Rate

Share

Share

Rate