Invalid column name 'deadlock_type'.

[bonfi]

Hi, with v4.0.0 and v3.6.1 i'm getting these errors:

 Running sp_Blitz... x (Failed)
  MSg 207, Level 16, State 1, Line 3436
 Invalid column name 'deadlock_type'.

Running sp_BlitzLock for all user databases... x (Failed)
  MSg 207, Level 16, State 1, Line 3715
 Invalid column name 'deadlock_type'.

Version of Sql Server is: 2019 (RTM-CU23) (KB5030333) - 15.0.4335.1 (X64)

I have not tested older versions of PSBlitz but if needed I can do it. Thanks

[VladDBA]

Hi @bonfi,

That's interesting. Do you get the same error if you run the sp_Blitz and sp_BlitzLock stored procedures from Brent's latest release? Also, what collation does that instance have? Meanwhile, I'll look into it on my side as well.

Thank you,

Vlad

[VladDBA]

quick update: As of now I'm unable to reproduce this on an instance running the exact same build as yours. What's interesting is that I'm not even sure how the first error can occur since sp_Blitz doesn't contain any reference to a column named deadlock_type.

[bonfi]

Hi @VladDBA,

i don't know why but I keep having this problem, both if I connect remotely to this sql instance and with local connection, like this:

`PS C:\Users\xxx.xxxx\Downloads\PSBlitz_v4.0.7\PSBlitz>  .\PSBlitz.ps1 localhost -IsIndepth Y -CheckDB DB_MOVIMENTI
Testing connection to localhost... √
->Estimated response latency: 0.016 seconds
Checking existence of database DB_MOVIMENTI...
->Database DB_MOVIMENTI - is online
Could not open Excel.
->Switching to HTML output.
Warning: Instance uptime is less than 7 days - 0.17
->Diagnostics data might not be reliable with less than 7 days of uptime.
--------------------------------------------------------------------------------
       Starting in-depth database-specific check for localhost
--------------------------------------------------------------------------------
 Starting BlitzWho background process... √
 ->sp_BlitzWho will collect data every 10 seconds.
 Retrieving instance information... √
 Retrieving TempDB usage data... √
 Getting open transaction info for DB_MOVIMENTI... √
 ->No open transactions found.
 Getting database info for DB_MOVIMENTI... √
 Running sp_Blitz... x (Failed)
  MSg 207, Level 16, State 1, Line 3436
 Invalid column name 'deadlock_type'.
 Running sp_BlitzFirst @Seconds = 30... √
 Running sp_BlitzFirst @SinceStartup = 1... √
 Running sp_BlitzCache for DB_MOVIMENTI
 ->Running sp_BlitzCache with @SortOrder = 'CPU'... √
 ->Running sp_BlitzCache with @SortOrder = 'Average CPU'... √
 ->Running sp_BlitzCache with @SortOrder = 'Reads'... √
 ->Running sp_BlitzCache with @SortOrder = 'Average Reads'... √`
....
....
 Running sp_BlitzIndex for DB_MOVIMENTI
 ->Running sp_BlitzIndex with @Mode = 1... √
 ->Running sp_BlitzIndex with @Mode = 2... √
 ->Running sp_BlitzIndex with @Mode = 4... √
 Running sp_BlitzLock for DB_MOVIMENTI... x (Failed)
  MSg 207, Level 16, State 1, Line 3715
 Invalid column name 'deadlock_type'.
 Getting stats info for DB_MOVIMENTI... √
 ->No rows returned.
 Getting index fragmentation info for DB_MOVIMENTI... √
 ->No rows returned.
 Stopping BlitzWho background process... √
 ->Successful runs: 16
 Retrieving sp_BlitzWho data... √
--------------------------------------------------------------------------------
Execution completed in: 00:03:40
Generated files have been saved in:
 C:\Users\xxx.xxxx\Downloads\PSBlitz_v4.0.7\PSBlitz\localhost_DB_MOVIMENTI_202403191046\

--------------------------------------------------------------------------------

If i run sp inside sql server it works:

EXEC	[AdminDB].[dbo].[sp_BlitzLock] @DatabaseName = DB_MOVIMENTI
GO

Could it be a powershell cache problem related to old PSBlitz.ps1 executions?

[VladDBA]

Hi @bonfi,

I still wasn't able to figure out why this is happening on your instance.

Can you please do the following:

1. provide the output for the following query (I'm doubtful it's a collation issue, but I'd rather be safe):

SELECT SERVERPROPERTY('Collation');

2. Try running in SSMS the scripts actually used by PSBlitz that end up throwing the error:

   • spBlitz_NonSPLatest.sql In spBlitz_NonSPLatest.sql replace line 37 that currently looks like this ;SET @CheckUserDatabaseObjects = 0; with this ;SET @CheckUserDatabaseObjects = 1, @Debug = 1; and then run it.

   • spBlitzLock_NonSPLatest.sql In spBlitzLock_NonSPLatest.sql replace line 28 that currently looks like this ;SET @DatabaseName = NULL; with this ;SET @DatabaseName = 'DB_MOVIMENTI', @Debug = 1; and then run it.
     If they both still error out then at least we know it's not PS acting weird. If you do get any error for either of them in the Messages tab, please add the whole debug message in a reply here.

3. Since it's a database error in both cases, I'm less inclined to think that it's a PS cached variables issue, especially since I'm guessing you've restarted PS at least a few times in the meantime.
   But, in any case, I've pushed a tentative fix for this in the dev branch. Since you already have the latest version of PSBlitz, just replace your existing 4.0.7 PSBlitz.ps1 script with this one and run it just like you normally would.