1

sp_BlitzFirst did not complete execution even after running it for more than 4 hours.

I have tried executing it with no parameters and with parameters as well, as shown below.

exec sp_BlitzFirst @seconds=10,@expertmode=1,@blitzcacheskipanalysis=1

We were able to run sp_Blitz succcessfully. How to make sure sp_BlitzFirst completes running?

The SP generated these messages:

Setting up configuration variables
Now starting diagnostic analysis
Checking for Read intent databases to exclude
Determining SQL Server version.
Capturing first pass of wait stats, perfmon counters, file stats
Beginning investigatory queries

(No other message is seen)

Specifications:

  • sp_Blitz ver 8.07
  • SQL Server Standard 64 bit, 2017
  • 16GB RAM alloted.
Improve this question
3
  • 2
    does sp_whoisactive show any blocking during the execution of sp_BlitzFirst?
    – Ronaldo
    Commented Dec 17, 2021 at 11:16
  • No blocking sessions.
    – IT Researcher143
    Commented Dec 17, 2021 at 12:25
  • 3
    I've seen this happen for the checks that run for long running blocking queries, and recently updated statistics. Can you use WhoIsActive to identify which query is stuck?
    – Erik Darling
    Commented Dec 17, 2021 at 13:51

2 Answers 2

Reset to default
1

I've seen this before on SQL Server 2019 Enterprise CU8. We cut over a large conversion project from Oracle to SQL. Conversion program ran for 1+ days in SQL Server. (8 cores not overloaded and no low memory issues.) Mostly historical data so didn't matter in regards to time. I had the blitz procedures capturing data to table every 15 mins via SQL Agent as Dr. Ozar suggested. About half way thru the conversion program runtime (12 hours?), the SQL Agent scheduled sp-blitz were running past their 15 min interval. Sorry don't remember the actual times but it was a "What the heck" moment. I ended up stopping the collection agent job. After the 1+ days running conversion program finished - the sp_blitz job runtimes were all back to normal. Its almost as if the DMV's were bloated with data from long running program - and sp_blitzfirst ran for much much longer times.

Improve this answer
0

Last week I had this problem with a customer, too.

To see more output from sp_BlitzFirst you can turn debugging information on by executing with parameter @Debug = 1.

It turned out that sp_BlitzFirst got stuck at check #44 (Statistics Updated Recently, as Erik Darling correctly suspected).

Neither sp_WhoIsActive nor sp_BlitzWho showed blocked sessions, too.

I have (temporarily) solved the problem by commenting out the code for this check.

Improve this answer

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