System Stats out of sync

I have two databases that are in an availability group, one as the primary replica and the other is the secondary. I have a store procedure that returns the following error.

DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Msg 2767, Level 16, State 1, Procedure xxxx.sys.sp_table_statistics2_rowset,
Line 105 [Batch Start Line 2]
Could not locate statistics '_WA_Sys_00000026_0143D405' in the system catalogs.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.

when I run the following query on my secondary replica(read-only):

SELECT OBJECT_NAME(s.object_id) AS object_name
    , COL_NAME(sc.object_id, sc.column_id) AS column_name
    , s.name AS statistics_name  
FROM sys.stats AS s 
INNER JOIN sys.stats_columns AS sc ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id  
WHERE s.name like  '%_WA_Sys_00000026_0143D405%'
ORDER BY s.name;

the stat appears:

STOJOU QTYPCU_0          _WA_Sys_00000026_0143D405
STOJOU QTYPCU_0          _WA_Sys_00000026_0143D405_readonly_database_statistics

when I run the query on the Primary replica it does not appear in the system tables but when you look under the table STOJOU it does appear.

How do I rebuild and/or resync the stats between my two systems? I am unable to drop/or create the Stat on my primary. Do I need to stop my synchronization between the primary and secondary to address this? Thanks for your assistance.

This is an outstanding bug in SQL Server which has been ignored for quite some time, unfortunately.

You can track down the offending statistic and drop/recreate it, or as some others in the above thread have suggested:

Clearing the userstore_dbmetadata related pools on ASYNC commit resolves the issue.
DBCC FREESYSTEMCACHE (‘dbname’)

Or create a stored procedure to check if the instance is an ASYNC commit, then clear the cache using an agent job.

CREATE PROCEDURE [dbo].[sp_clear_dbmetadatacache]
AS
SET NOCOUNT ON
DECLARE @dbname SYSNAME
DECLARE @linebreak AS VARCHAR
SET @linebreak = CHAR + CHAR

CREATE TABLE #dbcc
(
command VARCHAR
)

DECLARE db_cursor CURSOR FOR
SELECT DISTINCT name
FROM sys.sysdatabases db
INNER JOIN sys.dm_hadr_database_replica_cluster_states dbcs
ON db.name = dbcs.database_name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO dbname WHILE @FETCH_STATUS = 0
BEGIN
CREATE TABLE #avmode
(
availability_mode_desc VARCHAR
)
DECLARE value AS VARCHAR(20) DECLARE @sqlcmd2 AS NVARCHAR(150) SET @sqlcmd2 = 'SELECT DISTINCT availability_mode_desc FROM sys.availability_replicas WHERE replica_server_name = @SERVERNAME’
INSERT INTO #avmode
EXECUTE sp_executesql @sqlcmd2
SELECT @value = availability_mode_desc
FROM #avmode
BEGIN
IF @value = ‘ASYNCHRONOUS_COMMIT’
GOTO A
ELSE
GOTO B
END
A:
DECLARE @sqlcmd AS NVARCHAR
SET @sqlcmd = ‘DBCC FREESYSTEMCACHE (’‘’ + @dbname +’‘’) ’+ @linebreak + ’GO’
INSERT INTO #dbcc EXECUTE sp_executesql @sqlcmd
—PRINT @sqlcmd
B:
DROP TABLE #avmode
FETCH NEXT FROM db_cursor INTO @dbname;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;

GO


StackExchange Database Administrators Q#275256, answer score: 6