sp_AskBrent, when run as an agent job every 5 minutes is logging to a table but never seems to populate QueryText. However, sp_WhoIsActive, when also run every 5 minutes on the same SQL Server instance does capture the queries. I'll get an Execution Plan (QueryPlan) every so often, but that only seems to show up during MP tasks like DB backups.
- Am I invoking sp_AskBrent incorrectly, e.g. do I need to add an additional parameter? If so, which parameter(s)?
Here's what I'm using in job step of the agent job:
EXEC sp_AskBrent @OutputDatabaseName = 'DbaTools', @OutputSchemaName = 'dbo', @OutputTableName = 'AskBrentResults'
- Does this functionality not exist in sp_AskBrent? I'm using AskBrentVersion 23 according to the table results). Didn't think BlitzFirst was anything but a name change, so I'm using what I thought was the latest sp_AskBrent.
Am testing both sp_AskBrent and sp_WhoIsActive run periodically to see which may be better for having a general idea of what's going on with my instance over time. Seems that sp_AskBrent wants to show me its summation of issues only when logging to a table.
Here's how I invoke sp_WhoIsActive as a job step in an agent job and it's returning query text and execution plans:
DECLARE @destination_table VARCHAR(4000) = '[DbaTools].dbo.WhoIsActiveResults' ; EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1, @destination_table = @destination_table ;
- Has running one or both of these scripts every 5 to 10 minutes as an agent job provided proven value / saved your bacon in terms of follow-up troubleshooting when something went wrong after hours? I realize these scripts are typically run ad hoc in response to users asking about SQL Server "being slow." But I find the logging to table options intriguing.