Sql-server – sp_BlitzIndex – “Cannot Insert the value null into column ‘index_sanity_id’…”

sp-blitzindexsql server

Hi I'm getting this error message from the latest version of sp_BlitzIndex on one of my test servers:

database failed to process. Cannot insert the value NULL into column
'index_sanity_id', table
'tempdb.dbo.#IndexSanitySize____________________________________________________________________________________________________00000002626F'; column does not allow nulls. INSERT fails.

Best Answer

That typically happens when indexes are being added or dropped at the same time sp_BlitzIndex runs. If you have code that is dynamically adding/dropping tables in a user database, for example, that'll trigger this. The Github issue for that is tracked here.

To test that theory, try running sp_BlitzIndex again at a time with lower end user activity, or restore the same database to another server with no activity. (Granted, that's not going to get you index utilization data, but it'll prove out the theory.)

If you still get the error even with no activity on the server (again, restored to a different server so you can make sure), then we would need to step through the code to understand what kinds of indexes on your database are triggering the error. You can see it by modifying this part of the T-SQL:

    CREATE TABLE #IndexSanity
        (
          [index_sanity_id] INT IDENTITY PRIMARY KEY,

Change that to just:

    CREATE TABLE #IndexSanity
        (
          [index_sanity_id] INT IDENTITY NULL,

And then at the end of the proc:

    SELECT * FROM #IndexSanity WHERE index_sanity_id IS NULL

That way you can see which indexes are triggering the error, and we can figure out how to exclude them from being checked.

I created an issue in Github to track this and fix it in the next build:

sp_BlitzIndex - null error when populating #IndexSanitySize

Related Solutions

Sql-server – TSQL change column constraint between null and not null

When trying to return the column back to NOT NULL SQL Server immediately checks the constraint, which fails because the rows for that column were set to NULL.

For an existing column, you have to update the table to remove any NULL values before you can set it as NOT NULL.

Sql-server – Does the Inserted table contain Identity values

The error is caused by attempting to insert AuditTerms when there is no corresponding primary key in TermKeys. After creating the table schema and trigger in tempdb and inserting a few records in TermKeys, I was able to reproduce it by issuing a DELETE twice on the same TermID:

DELETE FROM TermKeys
WHERE TermID = 1

DELETE FROM TermKeys
WHERE TermID = 1

gives me:

Msg 515, Level 16, State 2, Procedure TRG_TermKeys_AuditTerms_IUD, Line 15 Cannot insert the value NULL into column 'TermID', table 'tempdb.dbo.AuditTerms'; column does not allow nulls. INSERT fails. The statement has been terminated.

Something in the application code may be running the query twice; I can't see any other way to get a null value. The best way to avoid the error is to add a precautionary check of @@ROWCOUNT at the beginning of the trigger, since it will return the total number of rows affected by the DML statement that fired it.

ALTER TRIGGER [dbo].[TRG_TermKeys_AuditTerms_IUD] ON [dbo].[TermKeys] 
AFTER INSERT, UPDATE, DELETE NOT FOR REPLICATION AS
BEGIN
  -- If the trigger was fired but no rows were affected, do nothing 
  IF @@ROWCOUNT = 0
    RETURN;
  SET NOCOUNT ON;
  DECLARE @termid int;
  IF EXISTS(SELECT * FROM Deleted) SELECT @termid = TermID FROM Deleted;
  IF EXISTS(SELECT * FROM Inserted) SELECT @termid = TermID FROM Inserted;
  IF EXISTS(SELECT TermID FROM AuditTerms WHERE (TermID = @termid))
  BEGIN
    UPDATE AuditTerms SET UpdatedAt = getdate() WHERE (TermID = @termid);
  END
  ELSE
  BEGIN
    INSERT INTO AuditTerms (TermID, UpdatedAt) VALUES (@termid, getdate());
  END;
END;

Best Answer

Related Solutions

Sql-server – TSQL change column constraint between null and not null

Sql-server – Does the Inserted table contain Identity values

Related Question