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.
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.
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.