Could Not Locate Statistics In The System Catalogs of Secondary Replica

Recently I encountered an error that I have never seen before:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Msg 2767, Level 16, State 1, Procedure sys.sp_table_statistics2_rowset, Line 105
Could not locate statistics ‘_WA_Sys_00000058_XXXXXXXX’ in the system catalogs.

First, a little background

The above error was being returned to a reporting server accessing a read intent only secondary availability group replica. (Let’s call the AG, AG1.)

The AG is running on Windows 2016 and SQL Server 2017. This AG has a primary and a secondary replica that is configured to read intent only. Whenever a query ran from the report server (which would use a Linked Server to the secondary replica), the above error was be returned. (OH! By the way, this *just* started happening. It was working fine before.)

Second, Things I Did to Resolve It

Based on the articles I came across online, I did the following things thinking it would fix the issue (none of them did):

1. I updated all the statistics on the Primary Replica (using Ola’s script)
2. I dropped/recreated the Linked Server connection on the report server to the secondary replica (thinking this might be an issue)
3. I created a different Linked Server and a synonym on the report server
4. I gave the login in question (db_owner). This was useless because I am ‘sa’ on all the servers and was still getting the error.
5. Finally, I decided to create a linked server, but this time point it to the Primary replica just to see if my “select” query would error or return results. To my surprise, it did not error out and returned the expected data. So this started to make the secondary replica the culprit big time.

One article I came across during my research was a link to Azure feedback website dated back in Feb 2013 titled, “SQL2012 Could not locate Statistics on secondary replica”

The above article described what I was encountering. The original post is:

When querying a table thru a linked server, got the following error message.

Msg 2767, Level 16, State 1, Procedure sp_table_statistics2_rowset, Line 105
Could not locate statistics ‘_WA_Sys_00000007_47DBAE45’ in the system catalogs.

The database was a secondary replica, primary replica was fine. Ran dbcc show statistics on secondary replica, that failed to find statistics distribution. There was an entry in sys.stats on both. Drop statistics on primary, to resolve.

Don’t Read The Comments!

You know how “they” say, “Don’t read the comments!” That actually did not apply here. One of the users provided a query in the comments and instructions on how to go about fixing this issue, albeit temporarily.

User, Arsh, post a query with the following instructions (big props to Arsh!):

1. From the Primary Replica, take the statistic name in question and put in the WHERE clause below
2. Once you confirm the table/column that this statistic is a part of, create a new statistic and drop the existing (missing) one.

Issue Solved.

--Use the database in question
USE [databaseName];
GO

--Put the statistic name in the WHERE clause
SELECT s.Name as statName,
c.name ColumnName,
OBJECT_NAME(s.object_id) as [tableName]
FROM sys.stats s
INNER JOIN sys.stats_columns sc ON s.Object_id = sc.object_id
  AND s.stats_id = sc.stats_id
INNER JOIN sys.columns c ON sc.column_id = c.Column_id
  AND sc.object_id = c.object_id
WHERE s.name = '_WA_Sys_00000004_4A202281';

/*
Results should bring back the following columns:

statName 
ColumnName 
tableName

_WA_Sys_00000004_4A202281 
LineStatusCode 
RateComponentLine

Then run the following to drop the existing stat and recreate a new stat:
*/

CREATE STATISTICS StatsLineStatusCode ON RateComponentLine (LineStatusCode) WITH NORECOMPUTE;
GO

DROP STATISTICS RateComponentLine._WA_Sys_00000004_4A202281;
GO

**This is only a temporary fix as it can pop up again.