0

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.

closed as too broad by LowlyDBA, RDFozz, Erik Darling, McNets, hot2use Jan 17 '18 at 9:20

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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. – RDFozz Jan 16 '18 at 20:50
3

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.

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.

3

The Microsoft Tiger Team has posted a set of scripts that handle this for you. Check this link out

https://github.com/Microsoft/tigertoolbox/blob/master/Running-Blocked-Processes/view_Running_Blocked_processes.sql

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.

https://github.com/Microsoft/tigertoolbox

  • 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. – cdub Jan 16 '18 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. – Chris Lumnah Jan 17 '18 at 14:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.