Imagine the situation of a user who is trying to insert his\her information into a website and after clicking the submit button they wait 3 minutes to get the operation completed, one of the reasons for this bad situation can be a waiting “locking” case, the insert statement is being locked by another statement that locked the targeted table.
So, it is very important for DBAs to know how to deal with locking events occurs on SQL Server because this is one of the critical cases that affect the database\application operations and reliability.
- Target SQL Server instance --> the instance has the locking event
- Blocked session id --> session id for the locking victim
- Blocked script --> the script is being blocked
- Blocked database --> the target database for the session\script being blocked
- Blocking session id --> session id for the locking owner
- Blocking login --> the login for the blocking session
- Blocking host --> the machine name for the blocking session
- Blocking application --> the application used by the blocking session for connecting to SQL Server
- Blocking script --> the script that caused the locking
- Wait type --> the wait type for the locking event
- Waite time (min) --> the locking duration in minuets
- Wait resource --> information about the locked object(s)
- Date\Time --> date and time for the locking event
are dynamic management views that contain information about the current sessions and requests being executed on SQL instance and used here to get most of information like blocked sessions id, host name, program name …etc.
is a dynamic management function that contains information about the SQL script being executed for a specific session, it converts the SQL handle into a text valued column that contain the SQL script being executed and used here to get both blocking and blocked scripts.
Then the full SQL script should be as below:
And this is a sample for the result set of the query
Then, the first step is to create the locking table in our dbatoolsdb inside schema “lck”.