SQL2012 Could not locate Statistics on secondary replica - by AgentJeff

Status : 

 


36
0
Sign in
to vote
ID 780062 Comments
Status Active Workarounds
Type Bug Repros 9
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 Javier Villegas on 10/21/2016 at 6:43 AM
I can provide you my environment for you to check. On my server I do have a linked server poiting to a SENCODARY , which is Read/Only.

SELECT * FROM dbo.MyLocalTable f
left join Linked_Server.Database.dbo.Table1 csc with (nolock) on csc.X_id=f.X_id
where csc.X_id is null

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

Please contact me.

This is becoming a big issue for me

Regards
Javier Villegas
Posted by Shreya [MSFT] on 10/3/2016 at 10:47 AM
Thank you for taking out time and reporting this issue! We tried to reproduce this in-house, but we were unable to reproduce it. Would it be possible for you to provide a reproduction, so that we can identify the bug and get a fix out as soon as possible?

Thanks,
shreya
Posted by Ermal Sinani on 4/12/2016 at 1:26 AM
Three years and this is still happening. We have this issue on SQL 2014 SP1 CU5.
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!