Error message: Cannot insert the value NULL into column 'objectName', table 'DBA.dbo.dba_indexDefragLog'; column does not allow nulls."

[Crucial2K]

Hi,

I got the error in the subject line a few weeks back. It might be applicable to others that have the same issue.

In my case it's because I scan a large number of databases and some indexes have been dropped during the scan (due to maintenance processes from a 3rd party application) and no longer available in sys.objects. When dba_indexDefragStatus needs to be updated with the objectName (for instance) from sys.objects it leaves it as null. Therefore when updating dba_indexDefragLog from dba_indexDefragStatus the insert fails because the objectName is null.

The solution is change the Insert statement at the /* Log our actions */ section. Original section / * Log our actions */ INSERT INTO dbo.dba_indexDefragLog ( databaseID , databaseName , objectID , objectName , indexID , indexName , partitionNumber , fragmentation , page_count , dateTimeStart , sqlStatement ) SELECT @databaseID , @databaseName , @objectID , @objectName , @indexID , @indexName , @partitionNumber , @fragmentation , @pageCount , @datetimestart , @sqlCommand; Fix Add WHERE @objectName IS NOT null