Index dependent on column 'FirstSeen' causes Configuration Wizard failure when upgrading UDT

When upgrading UDT, the Configuration Wizard fails to complete. The following error is displayed:

Error while executing script- The index 'IX_UDT_Endpoint_Rogue_F7D67' is dependent on column 'FirstSeen'. ALTER TABLE ALTER COLUMN FirstSeen failed because one or more objects access this column.

or

Database configuration failed: Error while executing script- The index 'IX_UDT_IPAddressCurrent_RouterNodeID_3088D' is dependent on column 'FirstSeen'. ALTER TABLE ALTER COLUMN FirstSeen failed because one or more objects access this column.

Observed symptoms:

• The Configuration Wizard fails during the UDT upgrade process.

• The error references a UDT index (such as IX_UDT_Endpoint_Rogue_F7D67 or IX_UDT_IPAddressCurrent_RouterNodeID_3088D) that is dependent on the FirstSeen column.

• The wizard cannot proceed until the blocking index is removed.

During a UDT upgrade, the Configuration Wizard attempts to alter the FirstSeen column in one or more UDT database tables. The operation fails because one or more non-compatible indexes are still defined on that column, preventing SQL Server from completing the column alteration.

Resolution 1 – Drop the blocking index and re-run the Configuration Wizard

Use this resolution when the Configuration Wizard fails with a specific index name in the error message.

Warning: Back up the SolarWinds Orion database before making any changes.

1. Back up the SolarWinds Orion database.

2. Connect to the database using SQL Server Management Studio (SSMS) or the SolarWinds Database Manager.

3. Run the following command to drop the blocking index.

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

USE [SolarWindsOrion];
GO

DROP INDEX [IX_UDT_Endpoint_Rogue_F7D67]
ON [dbo].[UDT_Endpoint]
WITH (ONLINE = OFF);

4. Run the Configuration Wizard again

Resolution 2 – Identify and drop all blocking indexes

Use this resolution if the Configuration Wizard continues to fail with a different index name after completing Resolution 1.

1. Run the following query to identify all UDT indexes that reference the FirstSeen column:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

SELECT i.name AS IndexName,
       OBJECT_NAME(ic.object_id) AS TableName,
       c.name AS ColumnName
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE c.name = 'FirstSeen'
  AND OBJECT_NAME(ic.object_id) LIKE 'UDT%';

2. For each index returned by the query, drop it using the DROP INDEX syntax from Resolution 1, substituting the index name and table name from the query results.

3. Run the Configuration Wizard again.

Note: The specific index names may vary between environments. Use the query above to identify all blocking indexes rather than relying solely on the names referenced in the error message.

Example indexes for reference: