大网数据广告活动
数据库与应用安全

Error 2767: Query Failure on a Secondary Replica

· 52CE 我爱测

Learn how to work around error 2767, "Could not locate statistics", when queries fail on a secondary replica of an Always On availability group.

Applies to:  SQL Server, Azure SQL Database, Azure SQL Managed Instance

Summary

When you offload read-only workloads from a primary replica to a secondary replica of an Always On availability group, a query against the secondary replica can fail with error 2767, "Could not locate statistics". This article provides workarounds for SQL Server, Azure SQL Database, and Azure SQL Managed Instance.

Symptoms

When you offload read-only workloads to a secondary replica of an Always On availability group from a primary replica, you notice that a query against the secondary replica fails with the following error message:

Msg 2767, Level 16, State 1, Procedure sp_table_statistics2_rowset, Line <LineNumber> [Batch Start Line <LineNumber>]

Could not locate statistics '<StatisticsName>' in the system catalogs.

Note: The <StatisticsName> is the statistics generated by a query (AUTO_CREATE_STATISTICS is set to ON) that's running on the primary replica.

Cause

This issue occurs because an active transaction prevents the cache invalidation log record from accessing and refreshing the statistics on the secondary replica.

Workaround

To work around the issue, use one of the following methods as required:

  • SQL Server

    If the issue occurs in SQL Server, run the following command against the secondary replica to synchronize the cached statistics with the statistics on disk:

    DBCC FREESYSTEMCACHE('ALL')
    GO
    

    [!NOTE] Execution of the DBCC command must occur after the active transaction finishes.

  • Azure SQL Database

    If the issue occurs in Azure SQL Database, run the Invoke-AzSqlDatabaseFailover cmdlet to fail over the readable secondary replica:

    Invoke-AzSqlDatabaseFailover -ResourceGroupName "<ResourceGroupName>" -ServerName "<ServerName>" -DatabaseName "<DatabaseName>" -ReadableSecondary
    

    This cmdlet fails over the readable secondary replica of the database named <DatabaseName> on the server named <ServerName>.

  • Azure SQL Managed Instance

    If the issue occurs in a Business Critical service tier Azure SQL Managed Instance, run the Invoke-AzSqlInstanceFailover cmdlet to fail over to a read-only secondary node:

    Invoke-AzSqlInstanceFailover -ResourceGroupName "<ResourceGroupName>" -Name "<ManagedInstanceName>" -ReadableSecondary
    

    This cmdlet fails over the readable secondary replica of the Managed Instance named <ManagedInstanceName>.

  • SQL Server, Azure SQL Database, and Azure SQL Managed Instance

    For a more permanent workaround, replace the query-generated statistics with the permanent statistics. Follow these steps:

    1. To determine the table where the query-generated statistics are defined, run the following command against the primary replica by using the statistics name in the error 2767:

      SELECT object_name([object_id]) FROM sys.stats WHERE name='<StatisticsName>'
      

      After running the command, the table name <TableName> is returned.

    2. Run the DBCC SHOW_STATISTICS command by using the name of the table and statistics to determine the column name:

      DBCC SHOW_STATISTICS('<TableName>', '<StatisticsName>')
      GO
      

      The following results display the contents of the statistics <StatisticsName> in the table <TableName>, and you can see that the statistics are created in the column <ColumnName> of the table <TableName>.

      Name Updated
      <StatisticsName> <DateTime>
      All density Average Length Columns
      0.5 4 <ColumnName>
    3. To drop the query-generated statistics in the table <TableName> on the primary replica, run the following command:

      DROP STATISTICS <TableName>.<StatisticsName>
      GO
      
    4. To create the permanent statistics in the column <ColumnName> of the table <TableName> on the primary replica, run the following command:

      CREATE STATISTICS <PermanentStatistics> ON <TableName>(<ColumnName>)
      GO
      

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 10/19/2026
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。