2

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.

7

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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