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