5

All,

I am currently putting together a knowledge based article that we can hand to our helpdesk to run through in order to do some preliminary troubleshooting or information gathering. One of the things I did was create the ability (thanks to Brent, https://www.brentozar.com/askbrent) to provide non-sysadmin's the ability to run sp_whoisactive to output some details they could provide to dbas like myself in the ticket. The problem I am running into is I want to exclude "sql_text" from results, but I cannot seem to figure out how?

I understand you have the ability to filter the output when it goes to a table (@output_column_list param) but I am not having the agents export it to a table. Initially, I was under the impression that the "@output_column_list" was where I could adjust the columns I wanted to see in the results, but it is clearly only a parameter used to filter the output to a table. In my desperate attempt, I tried the above parameter anyways and would leave out "sql_text" from the output, but all that did was reposition the column order.

EXEC sp_WhoIsActive @output_column_list = '[dd%][session_id][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]'

Therefore, I want them to be able to copy the contents (with headers) to an excel sheet to attach. The less complicated work, the better as I fear if I provide the help desk with too much info it will confuse them.

Am I missing something? They will not have the ability to run any jobs or even look at logs, so their rights are already VERY limited.

7

There's an error in your command. If you remove the [%] at the end, you'll get the correct output.

Current: EXEC sp_WhoIsActive @output_column_list = '[dd%][session_id][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]'

Should be: EXEC sp_WhoIsActive @output_column_list = '[dd%][session_id][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks]'

As a side note: if you want to get the query plan, and associated locks (since you've got them in your column list), you'll need to use the @get_plans and @get_locks parameters in combination with the output list.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.