Sql-server – sp_Blitz taking too long to complete

sp-blitzsql serversql server 2014

I just installed sp_Blitz and it has been running for an hour and still won't complete. I am able to run sp_BlitzFirst and it takes about 3 minutes to run but does complete.

Any idea why it won't complete?

It finally failed after 1 hour and 11 minutes

There is insufficient system memory in resource pool 'internal' to run this query.

I have one small test database in Recovery Pending state, could that be the issue?

I have Activity Monitor open but don't see any category maxed out, Proc 32%, Waiting Tasks 0, DB I/O .2 MB/Sec, Batch Req. a few hundred.

I ran sp_Blitz; GO and also selected the common settings in the script at the bottom and tried that with the same results.

Environment specifications

  • sp_Blitz ver 6.4.
  • SQL Server Std 64 bit, 2014, SP2 Build 9600
  • 128GB of RAM with max set to 116GB
  • Single server with about 25 databases, one large Dynamics database, about 300 GB.

Best Answer

This can happen when the server is under heavy load, or some part of Blitz is getting blocked.

Rather than jump to Activity Monitor, run sp_BlitzWho, which you should have also gotten with the First Responder Kit. Go ahead and update your question with the exact command you ran "and also selected the common settings in the script at the bottom" doesn't tell me exactly what you ran. - Erik Darling

Try using @CheckUserDatabaseObjects = 0 to skip what's inside the user databases altogether. Then, when it's taking over, say, 90 seconds, run sp_BlitzWho in another window to see what query is currently running. Then you can copy/paste that into your question to help track it down.

Also, try sp_BlitzFirst SinceStartup = 1, and copy/paste the top result sets into a spreadsheet (or take a screenshot) - that'll help us use the top wait stats to understand what's happening on the server. - Brent Ozar

Related Question