SQL2012 Could not locate Statistics on secondary replica - by AgentJeff

Status : 

 


42
0
Sign in
to vote
ID 780062 Comments
Status Active Workarounds
Type Bug Repros 12
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 TallGuyPNW on 9/6/2017 at 2:08 PM
This happened to us this morning, and unfortunately, our database is too large to send in a backup.
We received the same error that others saw:
    Msg 2767, Level 16, State 1, Procedure sp_table_statistics2_rowset, Line 105 [Batch Start Line 0]
    Could not locate statistics '_WA_Sys_0000001B_2A363CC5' in the system catalogs.
What I found is that, on the secondary replica, there is a row in sysindexes and in sys.stats for this _WA statistic, but dbcc show_statistics returns the 2767 error. It seems like some of the metadata describing the statistic isn't coming across Always On.

I did the workaround - deleting the statistic from the primary replica and the problem went away. Until.... SQL decided that it really wanted a statistic on that table's column and recreated the statistic. The error has returned.

I think my only solution at this point is to drop the statistic again from the primary and immediately create a new nonclustered index for that column so SQL won't try again to create a statistic.
Posted by Kevin Farlee [MSFT] on 8/7/2017 at 10:14 AM
The mitigation is not to drop the index, but to drop the statistics that are causing the error:
https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-statistics-transact-sql
This is a much less disruptive operation.
The development team is aware of the issue, but has yet to be able to create a repro here where we can study the situation and understand what needs to be fixed.If anyone has a reliable repro, starting from either a database backup or a CREATE DATABASE, we'd love to see it.
Posted by Old Garey on 7/29/2017 at 8:20 AM
hi, mine sql server 2014 AG today got the same issue, can anybody tell me how to settle the issue? my table 40 m records, its like impossible to drop and recreate the index. thx
Posted by vadba2 on 6/28/2017 at 8:06 AM
I have been running a 5 node AG since June of 2015. I have seen about 20 occurrences of this issue in that time. It always happens on our reporting replica. Usually, if I look at the statistics properties on the secondary replica, the statistic is listed by has no columns. Today, two statistics were apparently corrupted. The columns were shown in the statistics properties, but we still had errors. Running the query directly on the target server did not throw any errors.
As stated by others, dropping on the Primary resolves the issue.
Posted by Kmartin on 3/30/2017 at 9:56 AM
I have seen this same behavior on multiple servers in our environment, on both SQL2012 and SQL2014 availability groups (asynchronous). Please provide an update on this issue.
Posted by Javier Villegas on 1/18/2017 at 10:34 AM
Hello

Any updates? we are still seeing this issue
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 Microsoft 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!