SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Handling with currently running and blocking sessions

By Sheraz Mirza,

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.

Total article views: 546 | Views in the last 30 days: 25
 
Related Articles
FORUM
FORUM

Session blocking performance

SQL 2005 Performance drop due to blocking session

FORUM

blocking

blocking

FORUM

blocking

how long it is blocking

FORUM

Variable datatype for creating dynamic query which exceeds 8000 charecters.

Variable datatype for creating dynamic query which exceeds 8000 charecters.

 
Contribute