Released: Who is Active v11.11

re: Released: Who is Active v11.11

Tony — Thu, 22 Mar 2012 22:00:24 GMT

Hi Adam,

might it be worth adding SET ANSI_NULLS, QUOTED_IDENTIFIER ON to the top of your sp_whoisactive script (given that these settings are saved with the object definition at creation time)? If the proc is created with them off, it errors when it uses the xml datatype methods (e.g. the @get_transaction_info option).

re: Released: Who is Active v11.11

Don Halloran — Fri, 23 Mar 2012 01:18:37 GMT

Hi Adam, great query, this answers at least half of my day to day questions on perfomrance, blocking etc all by itself.

One question: What are the units on the various columns? For instance, used memory, reads, writes: pages, kb ...?

re: Released: Who is Active v11.11

Adam Machanic — Fri, 23 Mar 2012 01:35:54 GMT

Tony: Maybe in the next version :-)

Don: Units are 8KB data pages unless otherwise specified.

re: Released: Who is Active v11.11

John Sansom — Fri, 23 Mar 2012 08:00:47 GMT

Brilliant! I cannot thank you enough. This utility procedure has proved incredibly valuable to myself and the many others I have introduced it to. It's become a vital tool for triaging sql servers. Thanks for all your efforts and sharing it with us.

re: Released: Who is Active v11.11

spe109 — Fri, 23 Mar 2012 08:40:04 GMT

Thanks for your hard work Adam. Great utility that is so very useful.

re: Released: Who is Active v11.11

Craig M. — Sat, 24 Mar 2012 14:03:45 GMT

Thank you.

I will update my machines with v11.11.

> I appreciate any comments or feedback.

"After April [2011] all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation."

I periodically check back for this document, but it does not seem to be available yet.

Could you please comment on the availability?

Can I expect this document sometime this year?

If not, could you indicate the target year?

Thank you.

re: Released: Who is Active v11.11

dmmaxwell — Fri, 30 Mar 2012 13:29:19 GMT

This proc is part of my default administrative install for every sql server in the environment, along with a job that uses it to keep a log of activity on the server. Thanks again for your work.

re: Released: Who is Active v11.11

Max — Wed, 18 Apr 2012 20:30:05 GMT

Hi Adam,

WOW what a great tool. I have no words to describe how awesome this dose the job. I have helped some developer pin point what is going with your tool(but only in test environment). But got few comments...

I am a professional oracle DBA and a seasonal SQL Server DBA. Our company has most of there apps running on oracle and very few apps running SQL SERVER (2005 and 2008). Due to that i only get call about sql server issue one in a blue moon....our SQL Server apps are pretty stable but they do mis-behave every once in a while... now to the actual comment part..

I do see that we have to create a SP in the master db, but is there a way to run this without creating a SP. The reason is due to CHANGE MANAGEMENT POLICY..we have a very strict policy about creating new stuff in production and it will not be approved....There is a great tool just like yours called SNAPPER in oracle world written by Tanel Poder ( http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper )...dose the same thing in oracle but without creating any SP in the DB. Its a anonymous block.

If your tool could do something similar, that would be awesome for people who have a strict change management policy. As its hard to explain why i want to create this(besides performance reason) to change management. Is this actually even doable ?

Again THANKS for your awesome work on this.

re: Released: Who is Active v11.11

Scott — Wed, 18 Apr 2012 22:02:51 GMT

Max, you can easily run it without making a proc. You just have to make some small changes:

Remove the word "OUTPUT" from line 142

Remove the closing parentheses from line 147

Remove the last two lines (the END and GO)

Remove lines 1-25, and replace them with the word "DECLARE"

Now it is just one big sql script that you can run without creating any permanent objects.

re: Released: Who is Active v11.11

max — Thu, 19 Apr 2012 12:27:39 GMT

Thanks scott, I did extactly what you said...but it dose not seem to work...i get all the below errors...

Msg 139, Level 15, State 1, Line 0

Cannot assign a default value to a local variable.

Msg 137, Level 15, State 2, Line 359

Must declare the scalar variable "@filter".

Msg 137, Level 15, State 2, Line 387

Must declare the scalar variable "@filter_type".

Msg 137, Level 15, State 2, Line 393

Must declare the scalar variable "@filter_type".

Msg 137, Level 15, State 2, Line 399

Must declare the scalar variable "@not_filter_type".

Msg 137, Level 15, State 2, Line 405

Must declare the scalar variable "@not_filter_type".

Msg 137, Level 15, State 2, Line 411

Must declare the scalar variable "@show_sleeping_spids".

Msg 137, Level 15, State 2, Line 417

Must declare the scalar variable "@get_plans".

Msg 137, Level 15, State 2, Line 423

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 429

Must declare the scalar variable "@format_output".

Msg 137, Level 15, State 2, Line 435

Must declare the scalar variable "@help".

Msg 137, Level 15, State 2, Line 799

Must declare the scalar variable "@output_column_list".

Msg 102, Level 15, State 1, Line 803

Incorrect syntax near ')'.

Msg 137, Level 15, State 2, Line 814

Must declare the scalar variable "@output_column_list".

Msg 137, Level 15, State 2, Line 848

Must declare the scalar variable "@format_output".

Msg 137, Level 15, State 2, Line 852

Must declare the scalar variable "@format_output".

Msg 137, Level 15, State 2, Line 857

Must declare the scalar variable "@format_output".

Msg 137, Level 15, State 2, Line 862

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 878

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 884

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 889

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 893

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 897

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 901

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 905

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 909

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 913

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 918

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 922

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 928

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 933

Must declare the scalar variable "@get_locks".

Msg 137, Level 15, State 2, Line 937

Must declare the scalar variable "@get_transaction_info".

Msg 137, Level 15, State 2, Line 941

Must declare the scalar variable "@get_transaction_info".

Msg 137, Level 15, State 2, Line 947

Must declare the scalar variable "@get_outer_command".

Msg 137, Level 15, State 2, Line 953

Must declare the scalar variable "@get_plans".

Msg 137, Level 15, State 2, Line 957

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 962

Must declare the scalar variable "@find_block_leaders".

Msg 137, Level 15, State 2, Line 976

Must declare the scalar variable "@get_additional_info".

Msg 102, Level 15, State 1, Line 985

Incorrect syntax near ')'.

Msg 137, Level 15, State 1, Line 989

Must declare the scalar variable "@output_column_list".

Msg 137, Level 15, State 2, Line 1008

Must declare the scalar variable "@output_column_list".

Msg 137, Level 15, State 2, Line 1014

Must declare the scalar variable "@destination_table".

Msg 137, Level 15, State 2, Line 1018

Must declare the scalar variable "@destination_table".

Msg 137, Level 15, State 2, Line 1024

Must declare the scalar variable "@destination_table".

Msg 137, Level 15, State 2, Line 1044

Must declare the scalar variable "@sort_order".

Msg 102, Level 15, State 1, Line 1048

Incorrect syntax near ')'.

Msg 137, Level 15, State 2, Line 1059

Must declare the scalar variable "@sort_order".

Msg 137, Level 15, State 1, Line 1157

Must declare the scalar variable "@sort_order".

Msg 156, Level 15, State 1, Line 1178

Incorrect syntax near the keyword 'AS'.

Msg 137, Level 15, State 2, Line 1228

Must declare the scalar variable "@return_schema".

Msg 137, Level 15, State 2, Line 1250

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 1262

Must declare the scalar variable "@get_locks".

Msg 137, Level 15, State 2, Line 1331

Must declare the scalar variable "@filter".

Msg 156, Level 15, State 1, Line 1640

Incorrect syntax near the keyword 'AS'.

Msg 102, Level 15, State 1, Line 1682

Incorrect syntax near ';'.

Msg 137, Level 15, State 2, Line 1715

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 3399

Must declare the scalar variable "@filter".

Msg 137, Level 15, State 2, Line 3406

Must declare the scalar variable "@output_column_list".

Msg 137, Level 15, State 2, Line 3594

Must declare the scalar variable "@output_column_list".

Msg 137, Level 15, State 2, Line 3639

Must declare the scalar variable "@get_full_inner_text".

Msg 156, Level 15, State 1, Line 3700

Incorrect syntax near the keyword 'ORDER'.

Msg 137, Level 15, State 2, Line 3742

Must declare the scalar variable "@get_outer_command".

Msg 137, Level 15, State 2, Line 3879

Must declare the scalar variable "@get_plans".

Msg 137, Level 15, State 2, Line 3923

Must declare the scalar variable "@get_plans".

Msg 137, Level 15, State 2, Line 3961

Must declare the scalar variable "@get_plans".

Msg 137, Level 15, State 2, Line 4020

Must declare the scalar variable "@get_locks".

Msg 137, Level 15, State 2, Line 4293

Must declare the scalar variable "@find_block_leaders".

Msg 137, Level 15, State 2, Line 4336

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 4545

Must declare the scalar variable "@output_column_list".

Msg 137, Level 15, State 2, Line 4673

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 4679

Must declare the scalar variable "@delta_interval".

Msg 102, Level 15, State 1, Line 4684

Incorrect syntax near ';'.

Msg 137, Level 15, State 2, Line 4693

Must declare the scalar variable "@destination_table".

Msg 137, Level 15, State 2, Line 5142

Must declare the scalar variable "@schema".

re: Released: Who is Active v11.11

Adam Machanic — Thu, 19 Apr 2012 14:24:49 GMT

Max: why don't you just create it in tempdb? Or even better, make it into a temporary stored procedure (just put a # before the procedure name). You're allowed to create temp tables, right? This is effectively the same thing. Much easier than editing.

re: Released: Who is Active v11.11

Chris — Fri, 27 Apr 2012 14:55:45 GMT

If possible, can you add another filter type? I would like to able to filter by session duration (or time). For example, we have a lot of replication agents hitting our servers and they run for a long time. If we're having a performance problem it is usually something that is only a few minutes old. So it would be handy to get a sp_whoisactive result set that only shows the last n number of minutes of sessions. Thanks.

re: Released: Who is Active v11.11

Adam Machanic — Fri, 27 Apr 2012 18:00:36 GMT

Hi Chris,

Yes, it's on the list. Ideally I'd like to create a simple, XML-based custom filter "dialect" that will let you filter by any number of things -- I've had numerous one-off requests for new filter types and I don't want to add them all individually. Stay tuned.

--Adam

re: Released: Who is Active v11.11

Phil Carter — Wed, 06 Jun 2012 07:35:47 GMT

Hey Adam

Another stellar job.

One thing though, I note in the v11.11 notes

"Added host_process_id to additional_info collection"

Umm, how do I get that output?

We have an awesome vendor application that users run via Citrix. It holds locks when an error message appears on the users workstation. This causes a ever increasing blocking chain in the DB.

We use the Host_name and Host_process_id columns to track down users that have walked away from their workstation and not responded to the error message, thus they hold up database processing for everything.

This is what I'm running

EXEC sp_WhoIsActive

@find_block_leaders = 1

, @get_task_info = 1

, @get_additional_info = 0

, @output_column_list = '[session_id][block%][login_name][host%][host_process_id][program%][sql_text][wait_info]'

, @sort_order = '[blocked_session_count] DESC'

Also, would be great to be able to list out only blocked/blocking sessions ;)

Cheers

Phil

re: Released: Who is Active v11.11

Adam Machanic — Wed, 06 Jun 2012 14:15:14 GMT

Hi Phil,

You want @get_additional_info = 1, not 0.

As for *only* seeing blocked and blocking sessions -- why? That doesn't seem especially interesting to me, to be honest. Ideally you'd deal with the blocking and then not need that option anymore :-)

re: Released: Who is Active v11.11

RIzwan Hassan — Fri, 22 Jun 2012 01:34:36 GMT

If I like to filter by DB name... what is the best way to do it. (I am new to SQL DBA role)

Thanks!

re: Released: Who is Active v11.11

Adam Machanic — Tue, 26 Jun 2012 16:01:03 GMT

Rizwan:

EXEC sp_whoisactive @filter_type = 'database', @filter = 'your DB name'

re: Released: Who is Active v11.11

Rizwan Hassan — Mon, 02 Jul 2012 13:07:35 GMT

Thank you and great work!

re: Released: Who is Active v11.11

Sergio Pacheco — Wed, 25 Jul 2012 14:20:52 GMT

Adam,

I think you mentioned documentation in an earlier post. Where can I find it? Also, i missed your "No more Guessing" Troubleshooting talk this last April. When/Where do you think you may have it again? Is the recording available for purchase?

re: Released: Who is Active v11.11

Adam Machanic — Tue, 07 Aug 2012 13:54:37 GMT

Sergio:

Sorry, the docs aren't finished yet. I've had a very busy year.

As for "No More Guessing," are you referring to the delivery at SQLbits? That unfortunately wasn't recorded, but keep your eyes open early next year as I'm sure I'll be delivering it at various places. (The rest of this year, not much will be going on.)

--Adam

re: Released: Who is Active v11.11

Rob Kraft — Mon, 13 Aug 2012 13:50:58 GMT

I just discovered sp_whoIsActive and am eager to try it after seeing all the feedback. I am trying to run it right now, but I get no results. I have not read the 50 blog posts, though I have read a scattered few in the attempt to make this stored proc show results. Are there any usage examples?

I tried no parameters, and I get no records returned no matter which database I run it in. There are plenty of active sessions by users per sp_who.

I also tried running it with this example but still got no results.

EXEC sp_whoisactive @filter_type = 'database', @filter = 'your DB name'

My dbms in SQL2005, my tooling is SQL 2012.

Should I expect output when ran with no parameters? Could I be missing a step in setting it up to work?

Thanks for your time and devotion.

re: Released: Who is Active v11.11

Adam Machanic — Mon, 13 Aug 2012 14:33:02 GMT

Hi Rob,

Should you expect output when run with no parameters? Only if there's something interesting to show you.

This post explains it: http://sqlblog.com/blogs/adam_machanic/archive/2011/04/05/less-data-is-more-data-a-month-of-monitoring-part-5-of-30.aspx

--Adam

re: Released: Who is Active v11.11

DanOrc — Mon, 13 Aug 2012 14:35:51 GMT

Rob, the procedure must be run on master database and you will see transactions that are currently running, not the finished ones.

re: Released: Who is Active v11.11

Adam Machanic — Mon, 13 Aug 2012 14:42:17 GMT

Hi DanOrc,

Actually, the proc does not need to be deployed, nor run, in master. It can be put in any database you like. I personally like to put it in master because then it's available everywhere else without using a three-part name. But the decision is yours.

--Adam

re: Released: Who is Active v11.11

Rob Kraft — Mon, 13 Aug 2012 17:35:19 GMT

Thanks Adam! That makes sense and

EXEC sp_WhoIsActive @show_sleeping_spids = 2

is just what I needed to start getting oriented with the proc!

re: Released: Who is Active v11.11

Max — Mon, 20 Aug 2012 14:55:41 GMT

Hi Adam,

I have been using who is active tool for couple of months now and it has already saved us so many time. But i had one question for you. The other day someone requested me to capture the output of my query to see what exactly the SQL statements the session's are running and other metrics that who is active collects. So i thought okay, that is simple. Run your procedure and once i get the output in SSMS, right click on it and do "save results as". I exported that in quite a few different format(csv, excel, text file) but seems like the output is not readable in any of those file format.

I am in no means an expert, but if i were to guess that is happening due to the XML query SQL Text?

Do you have any suggestions as to how we can export this into a nice format(excel or csv)?

re: Released: Who is Active v11.11

max — Mon, 20 Aug 2012 17:13:03 GMT

I know you already have a post on capturing output using create table and such...but i wanted to see if there is anything besides that ?

re: Released: Who is Active v11.11

Adam Machanic — Mon, 20 Aug 2012 17:56:08 GMT

Hi Max,

Did you try turning off formatting and exporting only the non-XML columns that are left? (That would mean no query plan, for example.)

Aside from that I have no idea. Never tried exporting to Excel.

--Adam

re: Released: Who is Active v11.11

max — Mon, 20 Aug 2012 18:30:18 GMT

no i did not turn off formatting, how would i do that?

although i tried just export columns that are non-XML, but wanted to see if there was a way to export the XML column as well( sql text column)..

re: Released: Who is Active v11.11

Adam Machanic — Mon, 20 Aug 2012 18:33:35 GMT

If you turn off formatting SQL Text will no longer be XML.

EXEC sp_whoisactive @format_output = 0

re: Released: Who is Active v11.11

max — Mon, 20 Aug 2012 22:31:46 GMT

Thanks Adam, I tried that but no luck. Still the format seems to be out of order if i export it to CSV format.

re: Released: Who is Active v11.11

TheSeventhDawn — Mon, 17 Sep 2012 18:44:14 GMT

Adam,

First off love the tool. Is there a way to kick off this proc execution when spids are suspended? Blocked process threshold and blocked report are excellent but I would like to get the flavor of details that comes with this proc

thanks

re: Released: Who is Active v11.11

Chris at DEA not that DEA — Fri, 07 Dec 2012 19:49:58 GMT

Mike, I just found out about this sp at a local SQL users group meeting. This is fantastic stuff. Thank you so much for sharing this with us.

re: Released: Who is Active v11.11

Gianluca Sartori — Wed, 12 Dec 2012 10:46:00 GMT

Thanks Adam,

oustanding job as usual! I've been using WhoIsActive for years and it's part of my standard setup for every instance I install.

It would be great if we could display and filter context_info as well. Sometimes I find it useful to add troubleshooting info in that binary string and I had to tweak your code to use it.

Thanks again!

re: Released: Who is Active v11.11

Adam Machanic — Wed, 12 Dec 2012 14:41:33 GMT

Hi Gianluca,

I'll add it to the additional_info collection in the next version. (If you don't see it in the first beta, drop me a note and remind me.) Not sure I'll put a filter in for it, though. Probably not a very common request.

Best,

Adam

re: Released: Who is Active v11.11

max — Mon, 17 Dec 2012 18:00:04 GMT

Hi Adam,

I have been using your tool for quite a while. It works great. But the other day i wanted to try this using sqlcmd. And seems likes its not giving me output that it dose in SSMS....am i doing anything wrong. i tried the regular exec sp_whoisactive also tried disable the out format and stuff...here is what i get...

C:\oracle\sql>sqlcmd -S servername/instance_name -E

1> EXEC sp_whoisactive @format_output=0

2> go

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

2> EXEC sp_WhoIsActive

3> go

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

dd hh:mm:ss.mss

----------------------------------------------------------------------------------------------------------

00 00:08:35.220

Select Id From DWE_Workflows Where Id=-1

--?>

00 00:08:35.220

Select Id From DWE_Workflows Where Id=-1

--?>

00 00:08:35.216

Select Id From DWE_Workflows Where Id=-1

--?>

00 00:08:35.216

Select Id From DWE_Workflows Where Id=-1

--?>

00 00:07:28.373

Select Id From DWE_Workflows Where Id=-1

--?>

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

re: Released: Who is Active v11.11

Adam Machanic — Tue, 18 Dec 2012 14:34:09 GMT

Hi Max,

It seems to work fine on this end, although the output looks like absolute garbage; the proc's output is really optimized for SSMS. The option to turn off the formatting is there for data collection, and it's not going to help with SQLCMD output, since it doesn't decrease the large number of output columns, re-format XML, or anything else. What you'd need to do -- I think -- is collect into a table and then dump the output in a compact textual format. You'd also want to eliminate a number of the columns, such as the procedure text, which are going to add line breaks and ruin the output. But I'm not sure that's worth the effort. Any reason you can't fire up SSMS?

--Adam

re: Released: Who is Active v11.11

Max — Tue, 18 Dec 2012 18:16:59 GMT

We can fire up SSMS and make this run.

The other day we had an issue were we would only login thru SQLCMD, SSMS was taking very long time(like 15 mins to load)...were as SQLCMD was about 1-2 mins to load...

once in SQLCMD, we wanted to find out what is going on, who is taking the CPU, memory etc etc (which sp_whoisactive provided)...but when i ran spwhoisactive in sqlcmd, the output like you said was Unreadable....so we had to reboot the machine to get everything under control...

so i have been trying to figure out how to make sp_whoisactive work with SQLCMD....incase we have the issue again....Hope it makes sense...

re: Released: Who is Active v11.11

Max — Tue, 18 Dec 2012 18:39:26 GMT

Do you happen to have a quick output as to how it looks like ?? when you can collect into a table and then dump the output in a compact textual format. You'd also want to eliminate a number of the columns, such as the procedure text, which are going to add line breaks and ruin the output

re: Released: Who is Active v11.11

Adam Machanic — Tue, 18 Dec 2012 19:44:14 GMT

Hi Max,

I'm not sure what the output would look like as I've never done it :-)

I think I would do something like this, one block per session:

session_id: 123 host_name: xyz login: xyzzy

CPU: 123 Reads: 456 Writes: 789 Tempdb: 1234

Waits: (1x) CXPACKET (5x) PAGEIOLATCH_XX

[etc...]

---

re: Released: Who is Active v11.11

HIMANSHU — Fri, 11 Jan 2013 00:51:14 GMT

Hi Adam

I am getting this error

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

re: Released: Who is Active v11.11

Adam Machanic — Fri, 11 Jan 2013 16:10:41 GMT

Himanshu:

Ignore it. Maybe I'll make it go away in a future version, but it has no impact on anything.

--Adam

re: Released: Who is Active v11.11

HIMANSHU — Fri, 11 Jan 2013 19:15:21 GMT

Thanks Adam , I little modified it and running as script , its a awesome script .I have one question , under SQLtext column does it only show Tsql command (select,update etc) or it also shows exec procedure . Mostly we run storeprocs in any environmental ,if it shows store procs

it will be easy to find problematic sps .

It is human nature to always want more :-) .

Thanks again.

re: Released: Who is Active v11.11

Adam Machanic — Fri, 11 Jan 2013 19:39:30 GMT

Hi Himanshu:

Read this:

http://sqlblog.com/blogs/adam_machanic/archive/2011/04/10/commands-batches-and-the-mysteries-of-text-a-month-of-activity-monitoring-part-10-of-30.aspx

--Adam

re: Released: Who is Active v11.11

dbiDBA — Mon, 14 Jan 2013 19:26:36 GMT

I love to use your WhoIsActive functionality. Thanks for all your work. But I am having an issue. When the instance is busy and I need results the most, it frequently gives this error:

"The statement terminated. The maximum recursion 32767 has been exhausted before statement completion."

Originally, the maxrecursion was the default 100. I modified the code to increase the maxrecursion limit to the maximum and still frequent hit the limit. Is this something you have seen before? Is there a fix or workaround? Is there a runtime option I can avoid to prevent the error?

WhoIsActive 11.11 on SQL Server 2008 SP3

re: Released: Who is Active v11.11

Adam Machanic — Mon, 14 Jan 2013 20:18:07 GMT

Hi dbiDBA:

I am aware of the issue, and will fix it in the next version. The problem is that under load, there can be conditions where the blocking chain is nonsensical and seems to include cycles.

In the meantime, as a workaround, don't use @find_block_leaders mode when you're seeing that error. I realize that's not a fantastic workaround but it's all I can do for you today.

--Adam

re: Released: Who is Active v11.11

Shiju Samuel — Mon, 28 Jan 2013 13:49:49 GMT

Hi Adam, Is there a way to find out how long a victim of blocking is clogged? -Shiju

re: Released: Who is Active v11.11

Adam Machanic — Mon, 28 Jan 2013 14:36:11 GMT

Shiju: Yes; a blocked session will have a LCK_M_* wait in the wait_info column. The wait will include a wait time in milliseconds.

--Adam

re: Released: Who is Active v11.11

Dustin Mueller — Wed, 06 Feb 2013 17:26:00 GMT

I have to say thank you for all of your hard work that you put into this, because I have found myself using it so often!

re: Released: Who is Active v11.11

Chris — Thu, 28 Feb 2013 13:42:45 GMT

Adam, is there a help file for using your tool? Or is all of that available in comments inside the proc?

re: Released: Who is Active v11.11

Adam Machanic — Thu, 28 Feb 2013 14:41:39 GMT

@Chris: EXEC sp_whoisactive @help = 1

re: Released: Who is Active v11.11

yazalpizar — Thu, 14 Mar 2013 12:07:40 GMT

Hi Adam, thanks a lot for this great tool, I'm just scratching the surface and it's already really helpful troubleshooting our databases. One question though: I'm going through all your 30 blog posts on april'11 about monitoring and you mentioned the possibility of having all them on one single downlodable file. Does that file exists or you haven't had the time yet to compile all the information? I will continue reading online, but would be great to have it on pdf or any other format that could be used on ebook reader. Thanks again!

re: Released: Who is Active v11.11

NewKidInTown — Thu, 18 Apr 2013 14:10:47 GMT

I tried to run the script to create the sp and I get the following error:

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

Incorrect syntax near '.'.

Msg 156, Level 15, State 1, Procedure sp_WhoIsActive, Line 3941

Incorrect syntax near the keyword 'CASE'.

Msg 156, Level 15, State 1, Procedure sp_WhoIsActive, Line 3979

Incorrect syntax near the keyword 'CASE'.

Can someone please help?

re: Released: Who is Active v11.11

Adam Machanic — Fri, 19 Apr 2013 03:56:41 GMT

@NewKidInTown: Your database compatibility level is set incorrectly. Change it to at least 90 (SQL Server 2005). This is a common issue for instances that were upgraded from 2000; databases, including master, are not automatically migrated to higher compatibility levels.

re: Released: Who is Active v11.11

Mike Stuart — Tue, 23 Apr 2013 15:10:26 GMT

Adam, thanks for the awesome work on this.

Suggestion / question: we have a LOT of instances in our environment; how about a @version parameter? For example: 'exec sp_WhoIsActive @version = 1' would return something like 'Who Is Active? v11.11 (2012-03-22)', instead of needing to run sp_helptext, scripting out the proc, etc. on every instance in our environment?

re: Released: Who is Active v11.11

Adam Machanic — Tue, 23 Apr 2013 15:34:16 GMT

Hi Mike,

EXEC sp_whoisactive @help = 1

:-)

re: Released: Who is Active v11.11

Mike Stuart — Tue, 23 Apr 2013 15:49:19 GMT

... and there it is! Excellent turnaround time, and I didn't even have to download it! You're truly amazing... or I'm an idiot :P Thanks.

Landed as BI, but databases are a big WTF, what to do? | Question and Answer

Landed as BI, but databases are a big WTF, what to do? | Question and Answer — Sun, 28 Apr 2013 07:24:42 GMT

PingBack from http://qandasys.info/landed-as-bi-but-databases-are-a-big-wtf-what-to-do/

Kai Borgolte » Blog Archive » How to Find Slow SQL Server Queries

Kai Borgolte » Blog Archive » How to Find Slow SQL Server Queries — Mon, 29 Apr 2013 09:54:38 GMT

PingBack from http://www.borgolte.ch/?p=714

re: Released: Who is Active v11.11

Jordon (HeavenCore) — Mon, 29 Apr 2013 14:05:57 GMT

Hi there,

Is there a script to drop sp_WhoIsActive and all its dependencies?

re: Released: Who is Active v11.11

Cary Davis — Mon, 29 Apr 2013 15:32:16 GMT

This thing is cool.

Now I've got the power to control the world from right here at my desk.

This is great stuff, Thanks Adam. I wanna be you when I grow up. DO NOT ever lose those sunglasses man.

re: Released: Who is Active v11.11

Adam Machanic — Mon, 29 Apr 2013 16:11:33 GMT

@Jordan: DROP PROC sp_WhoIsActive; (But why you'd want to is beyond me :-))

@Cary: Cheers!

Five Ways to Improve Your Productivity with SSMS | DB NewsFeed

Five Ways to Improve Your Productivity with SSMS | DB NewsFeed — Tue, 30 Apr 2013 11:03:42 GMT

PingBack from http://www.dbnewsfeed.com/2013/04/30/five-ways-to-improve-your-productivity-with-ssms/

Estimating Remaining Query Time | FradenSQL

Estimating Remaining Query Time | FradenSQL — Tue, 07 May 2013 14:41:36 GMT

PingBack from http://www.fradensql.com/2013/05/estimating-remaining-query-time/