THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

The Node Knows (A Month of Activity Monitoring, Part 20 of 30)


Published Wednesday, April 20, 2011 11:00 AM by Adam Machanic



Michael Codanti said:


Thanks for Who Is Active, and the documentation you are writing, I am not only learning more about Who Is Active but SQL Server as well. (Both of which are good things.)

April 20, 2011 11:37 PM

spe109 said:

Hi Adam,

I'd just like to echo what Michael said in a previous post. Thanks for all your hard work both in writing the tool and writing this series of posts.



April 21, 2011 4:24 AM

Adam Machanic said:

My pleasure :-)

April 21, 2011 10:15 AM

Meher said:

Hi Adam

I am using SQL Server 2005 SP3 and I got the following error message when I ran the following in a original query window:

EXEC sp_WhoIsActive

   @get_task_info = 2,

   @get_plans = 1

I ran the update statement with a begin tran in the first(original) query window and in a second query window ran the Select Statement with the Row_number clause in it. Then back in the original query window I executed the sp_whoISActive.

Msg 3930, Level 16, State 1, Procedure sp_WhoIsActive, Line 3614

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 3998, Level 16, State 1, Line 1

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Is it because I did something wrong?



April 21, 2011 5:01 PM

Adam Machanic said:

Hi Meher,

Your transaction was killed due to an exception -- probably a timeout on getting a query plan. I don't recommend running Who is Active in the middle of a transaction; it's not designed for that.



April 21, 2011 7:37 PM

Meher said:

Hi Adam,

Thanks. That explains it why I got the error.



April 21, 2011 10:03 PM

Adam Machanic said:

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run

April 30, 2011 11:45 AM
New Comments to this post are disabled

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement