- Adam Thurgar
Whoisactive - logging table and schedule
I have blogged previously about what a great tool sp_whoisactive is from Adam Machanic. Sometimes you may need it to be run regularly to capture what is happening, instead of hoping if you run it interactively and you get the results you want. There is a script from Brent Ozar (and a few others) that will allow you to get the information from sp_whoisactive into a logging table. It also purges the table to make sure it doesn't get too large. You can then create a SQL Server Agent job that runs this script and then schedule it as required. Then you will need to query the logging table to hopefully find the issue. Just another way to be proactive in finding what is happening on your SQL Server.