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 write statement and execute
Now 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 which 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.
Other DBA related blogswindows services maintenance
automated backup with logs
tips for DBA
restore master database
sqlcmd export data to txt file
No comments:
Post a Comment