Microsoft.Data.SqlClient.SqlException (0x80131904): The statistics 'xxxxxxx' is dependent on column 'xxxxxxx'. ALTER TABLE DROP COLUMN xxxxxxxx failed because one or more objects access this column

News Knowledge Base
,
1

Facing issue during Orchestrator Upgrade " Microsoft.Data.SqlClient.SqlException (0x80131904): The statistics xxxxxxx is dependent on column xxxxxxx .ALTER TABLE DROP COLUMN xxxxxxxx failed because one or more objects access this column."

Issue Description:

Microsoft.Data.SqlClient.SqlException (0x80131904): The statistics xxxxxxx is dependent on column xxxxxxx .ALTER TABLE DROP COLUMN xxxxxxxx failed because one or more objects access this column.

Resolution:

  1. Execute the below SQL query in DB and all statistics will be available in DB tables with stats name, column name,table name, etc details.

SELECT DISTINCT

OBJECT_NAME(s.[object_id]) AS TableName,

c.name AS ColumnName,

s.name AS StatName,

s.auto_created,

s.user_created,

s.no_recompute,

s.[object_id],

s.stats_id,

sc.stats_column_id,

sc.column_id,

STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated

FROM sys.stats s JOIN sys.stats_columns sc

ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id

JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id

JOIN sys.partitions par ON par.[object_id] = s.[object_id]

JOIN sys.objects obj ON par.[object_id] = obj.[object_id]

WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1

AND (s.auto_created = 1 OR s.user_created = 1)

  • Output looks like below
  1. Now, whichever statistics will be creating the issue during upgrade, it is possible to search in statName column in the output and find the table name where that specific stats exists.
  2. Execute the following query to update SQL Server specific statistics on required table
    • Update STATISTICS
  3. Now, re-run the first query to recheck the statistics last updated column
  4. Proceed to upgrade the Orchestrator
  5. If still the issue persists, delete the statistics which are creating the issue from specific table which can be found using the first SQL query above.