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.

| improve this 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 (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.

| improve this answer | | | | |
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

| improve this answer | | | | |
  • 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.