SQL Server has a wealth of Dynamic Management Views (DMVs) to get you health status of the platform. All the data will be extracted with a query, set of transactions and many more ways, in this case you had to be careful with transactions, because whenever so much data was flowing back and forth between the application server and the database server, too many things could go wrong. Here is the quick DMV that can get you list of operations in active transaction of the current session declare @xactid...(
read more)
Read the complete post at http://sqlserver-qa.net/blogs/t-sql/archive/2010/01/27/6263.aspx