Sql-server – SQL Server 2008 R2 – Capture or be notified when an sql handle is ran

sql serversql-server-2008-r2

I'd like to monitor a particular SQL handle and find out who (process id) is running the SQL.

Is there a way to retrieve either a list of proc ids who executed the SQL handle in last N minutes or be automatically notified when that query is ran? or is only option is to run sys.dm_exec_requests continuously?

Best Answer

For SQL Server 2008 R2 or earlier, I'd recommend running a SQL Server Profiler session against the server.

On the General tab, in the template dropdown, select "Blank", then on the Events Selection tab, choose "SQL_StmtStarting" from the list of events. Click the "Column Filters" button, and fill it out similar to this:

enter image description here

When you run this profiler session, the results will only show you rows where the TextData column contains the text you entered. Alternately, if you have a sql handle you specifically want to track you can enter that in the filter under SqlHandle instead of TextData.

On SQL Server 2012 and above, I'd recommend using Extended Events to do this, however that is out of the scope of this question.

Related Question