Posted on by Mohammad Darab

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.

12 Replies to “Could Not Locate Statistics In The System Catalogs of Secondary Replica”

  1. We recently encountered the same Error 2767 (“Could not locate statistics”) when querying a readable AG secondary through a linked server. We are aware of the documented workarounds (DBCC FREESYSTEMCACHE(‘ALL’) and replacing the auto-created statistic with a permanent statistic), but during troubleshooting we noticed two additional observations that may be useful to others.

    In our environment, the problem only occurs when using a four-part name:

    SELECT TOP (1) *
    FROM [LinkedServer].[LWE_INFOR_P01].[dbo].[SomeTable];

    This consistently raises:
    Msg 2767
    Could not locate statistics ‘_WA_Sys_xxxxxxxxxxxxxxxx’ in the system catalogs.

    However, the error does not occur when accessing the same table through OPENQUERY:

    SELECT *
    FROM OPENQUERY
    (
    LinkedServer,
    ‘SELECT TOP 1 * FROM LWE_INFOR_P01.dbo.SomeTable’
    );

    Even more interesting, the original query succeeds when the database name is written with a different casing (one lowercase letter is enough):

    SELECT TOP (1) *
    FROM [LinkedServer].[lwe_infor_p01].[dbo].[SomeTable];

    The target database uses a case-sensitive collation, while the SQL Server instance itself is case-insensitive.

    At this point we cannot determine whether the database name casing is actually relevant or whether this is merely exposing a deeper metadata/statistics caching issue. We have only reproduced this behavior in a single environment so far and have not yet verified it across other databases or SQL Server versions.

    Nevertheless, the fact that OPENQUERY succeeds while the four-part name fails may indicate that the issue is related to linked server metadata retrieval (for example sp_table_statistics2_rowset) rather than the actual data access itself.

    If anyone else can reproduce this behavior, I would be very interested in hearing about it.

    Reply

  3. We see this too often — to solve it we run this on Secondary:
    DBCC FREESYSTEMCACHE(‘ALL’)

    Reply

  5. Fiddling with the statistics is not a solution, it’s a temporary fix which will happen again on some other random table as long as you’re using a linked server connection. Dropping the auto-generated statistic on the primary can cause a database recovery to kick off for all databases on the secondary replica.

    The only permanent fixes I could come up with:
    1. Query the primary replica instead of the secondary.
    2. Get rid of linked server connection and use OPENQUEY. Query string is limited to 8k.
    3. Set a up separate database to be used for reporting and keep it’s data up to date using backup/restore, replication or log shipping.
    4. Run your queries when nobody is on the primary replica making changes to the data.

    Reply

  6. This also happen to me and your post was instrumental in resolving the issue. Did you ever find a permanent solution to this issue?

    Reply

  7. This isn’t a solution. To resolve it permanently you just need to drop and re-generate stats on table in question

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.