Who is Active? v8.82 - Harder, Better, Faster, Stronger

re: Who is Active? v8.82 - Harder, Better, Faster, Stronger

Sankar Reddy — Thu, 20 Aug 2009 16:41:54 GMT

Thank You for updating it. This has been a valuable monitoring script in my environment and helped us many times without having to look at running multiple scripts.

re: Who is Active? v8.82 - Harder, Better, Faster, Stronger

AaronBertrand — Thu, 20 Aug 2009 18:17:20 GMT

Just so people don't think I'm being overly pedantic, my only complaint about the right-justified columns is that they don't work well if you use a fixed-width font in grid output (I use Consolas). For some reason it is right-padded by spaces equal to the number of digits in the value:

http://aaronbertrand.com/voodoo/who_is_active_output.png

I've been meaning to see if I could fix this, but have been distracted by way too many other things.

That all said, this is still a fantastic procedure and saves me a lot of manual work hunting for values in DMVs on my own. Keep up the great work Adam.

re: Who is Active? v8.82 - Harder, Better, Faster, Stronger

AaronBertrand — Thu, 20 Aug 2009 18:29:21 GMT

Sorry, not *equal* to the number of digits, but at least somewhat directly related.

re: Who is Active? v8.82 - Harder, Better, Faster, Stronger

Phil — Thu, 20 Aug 2009 23:35:27 GMT

Adam, just having an issue creating the sp on one of my SQL 2008 servers.

SQL Server 2005 Express with Advanced Services - 9.00.4035.00 -- OK

SQL Server 2008 (SP1) - 10.0.2531.0 -- OK

SQL Server 2008 (RTM) - 10.0.1600.22 -- FAILED

Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 394

Incorrect syntax near '.'.

Seem SQL 2008 RTM doesn't like the CROSS APPLY syntax, but 2005 Express is ok with it.

re: Who is Active? v8.82 - Harder, Better, Faster, Stronger

Adam Machanic — Fri, 21 Aug 2009 03:38:57 GMT

Hi Phil,

Interesting. It definitely works on 2008 RTM; are you by any chance trying to create the procedure in a DB set to a different compatibility level than 2005 or 2008? I get the exact same error and same line number if I set a database to SQL Server 2000 compatibility and try to create the proc in there.

re: Who is Active? v8.82 - Harder, Better, Faster, Stronger

casm — Fri, 21 Aug 2009 08:09:42 GMT

Great way to retrieve the information about the parameters!!. Hope you don't mind if I use it in my own store procedures.

And, one more time, thanks for this superb script.

re: Who is Active? v8.82 - Harder, Better, Faster, Stronger

Alvaro Mosquera — Fri, 21 Aug 2009 08:50:22 GMT

With version v8.82 the sp show several process with status "sleeping" runnig the query "exec sp_MSget_last_transaction @publisher_id = 0, @publisher_db = N'OracleSinfos', @for_truncate = 0x1". I think this process belong to the LogReader process of transactional replication and are active since the SQL start. The version v.8.40 don´t show this process.

Is there anayway to filter this process?.

Thanks.

re: Who is Active? v8.82 - Harder, Better, Faster, Stronger

Adam Machanic — Fri, 21 Aug 2009 13:45:31 GMT

Hi casm,

Enjoy!

re: Who is Active? v8.82 - Harder, Better, Faster, Stronger

Adam Machanic — Fri, 21 Aug 2009 13:45:50 GMT

Hi Alvaro,

This is a change I forgot to mention in the post. In the previous version of the script, it showed all active requests by default, if you asked for transaction information you would see all SPIDs either with active requests or with at least one open transaction. Several people asked me to change that behavior and always show the SPIDs with an open transaction. I guess now we have a counter-case. I'll consider adding a new param that lets you control that.

Quick, dirty, but effective – finding top CPU consumers

Linchi Shea — Fri, 21 Aug 2009 22:12:50 GMT

Despite all the DMVs, the sysprocesses table still offers a significant amount of information for identifying

re: Who is Active? v8.82 - Harder, Better, Faster, Stronger

Michelle Ufford — Mon, 24 Aug 2009 21:47:10 GMT

Adam,

Thank YOU for all of your hard work on this excellent script. I have now updated my servers to your latest version, and all is working well. Your efforts are much appreciated! :)

Michelle

Who is Active? v9.57: Fast, Comprehensive DMV Collection - What's Really Happening on Your Server?

Adam Machanic — Thu, 03 Dec 2009 20:50:32 GMT

Happy December, SQLblog readers! My gift to you, just in time for the holidays: The newest "official"

re: Who is Active? v8.82 - Harder, Better, Faster, Stronger

David Wetherell — Sat, 16 Oct 2010 19:06:36 GMT

Adam,

This is cool, I love the built in help parameter ...

David