You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
The text was updated successfully, but these errors were encountered:
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
The text was updated successfully, but these errors were encountered: