A lot of the code to do this is in the sp_WhoIsActive documentation.
But since I have to do things a little bit differently, I decided to open source the way I do things.
You can get the full script here: sp_WhoIsActive Logging
That’s the full script to the SQL Server Agent job I use.
The big tweak that I make to things is to create a new table for every day of logging. I dislike the approach of logging everything to one big table and automatically managing the retention for two reasons:
- You might delete something important
- It sucks querying giant tables full of query logging details
I sort of stopped short of automating creating date constraints on each table and creating a view to union them all together. I just don’t need to do that often enough.
The full command I use for logging is this:
EXEC sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @get_task_info = 2, @get_additional_info = 1, @find_block_leaders = 1, @get_memory_info = 1;
Which means you do have to be on the latest version of sp_WhoIsActive. If you haven’t updated in a while, uh… I promise it’s a fast and painless process.
Just hit F5.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.