have to monitor blocking queries in SQL Server 2012. How do we figure out which queries are the locked ones? Do I use the Activity Monitor? I want actual SQL statements.
1
-
Can you provide some sort of detail as to what you mean when you say "monitor"? When provided with an answer below, you responded additional information that might be critical to getting you the full answer you need. Please edit that information into the question.– RDFozzCommented Jan 16, 2018 at 20:50
Add a comment
|
2 Answers
Complementing to what @Chris suggested -
You should use sql agent alert and choose to either email you or log to a local database e.g. dbautility or dbaadmin (whatever you choose the name). This script from Andy Mallon is a great resource (and an article about it here).
Also, you can use sp_whoisactive with @find_block_leaders = 1 parameter to show you details - I have mine set as this gist. You can even log that to a table.
answered Jan 16, 2018 at 19:36
The Microsoft Tiger Team has posted a set of scripts that handle this for you. Check this link out
This will give you a lot of information about the processes running on your system. You can modify this to send the data to a table if that helps you track issues and do some trending.
Check out the rest of the scripts in the toolbox they have published.
-
does this work for say 10 minutes? how do i monitor for 10 minutes? Do I need to keep refreshing/rerunning the script? Thanks in advance.– cdubCommented Jan 16, 2018 at 19:29
-
If you modify the script to log to a table, you can then set it up as a job as @Kin suggested using the SQL Agent. Also, look at the resources that Kin has suggested as those are great alternatives to show you this information.– user103243Commented Jan 17, 2018 at 14:04