SQL2012 Could not locate Statistics on secondary replica - by AgentJeff

Status : 

 


16
0
Sign in
to vote
ID 780062 Comments
Status Active Workarounds
Type Bug Repros 5
Opened 2/26/2013 2:51:00 PM
Access Restriction Public

Description

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.
Sign in to post a comment.
Posted by SeyitTR on 6/15/2015 at 4:36 AM
Hi, still, this is also a bad issue for our production. At different times, for different tables of any databases that are participated in AG , we experience this problem and our temporary solution is to delete the stat on primary replica every time it occurs.
MS, Please suggest a permanent workaround to this annoying case, really need help !
Posted by aG_ on 5/20/2015 at 5:55 PM
*Remove the first temp table and the batch separators. Not sure why I put that initially :|
Posted by aG_ on 5/19/2015 at 11:48 PM
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(4)
SET @linebreak = CHAR(13) + CHAR(10)

CREATE TABLE #dbcc
    (
        command VARCHAR(max)
    )

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(20)
        )
     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(max)
     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
Posted by aG_ on 5/4/2015 at 3:39 AM
Microsoft folks - Can we *please* have some kind of explanation on why this happens for dynamic stats and on the asynchronous node only? It has been a real pain for us to manually drop the statistic on the primary node to clean up the corrupt statistic on the asynchronous secondary node.
Posted by andrewblack on 4/2/2015 at 1:38 AM
>A real pain!
+1
Posted by Y Wahnon on 7/9/2014 at 11:04 PM
This is happening to us, at least 3 times a week, on different tables in the database. The drop statistics on primary works, but the processes that are meant to run on the secondary via, linked servers are failing! A real pain!