Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

SQL2012 Could not locate Statistics on secondary replica

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.

29 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 48

<=-=Jul 9 2014 11:04PM=-=>

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!

<=-=Apr 2 2015 1:38AM=-=>

>A real pain!
+1

<=-=May 4 2015 3:39AM=-=>

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.

<=-=May 19 2015 11:48PM=-=>

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

<=-=May 20 2015 5:55PM=-=>

*Remove the first temp table and the batch separators. Not sure why I put that initially :|

<=-=Jun 15 2015 4:36AM=-=>

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 !

<=-=Apr 12 2016 1:26AM=-=>

Three years and this is still happening. We have this issue on SQL 2014 SP1 CU5.

<=-=Oct 3 2016 10:47AM=-=>

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

<=-=Oct 21 2016 6:43AM=-=>

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

<=-=Jan 18 2017 10:34AM=-=>

Hello

Any updates? we are still seeing this issue

<=-=Mar 30 2017 9:56AM=-=>

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.

<=-=Jun 28 2017 8:06AM=-=>

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.

<=-=Jul 29 2017 8:20AM=-=>

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

<=-=Aug 7 2017 10:14AM=-=>

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.

<=-=Sep 6 2017 2:08PM=-=>

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 showstatistics 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.

14 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • Heitor Zabala Marcos - Globalweb commented  ·   ·  Flag as inappropriate

    I had this issue today in my bi environment.
    Reading how AO works with statistics in the following link:
    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups?view=sql-server-2017#Read-OnlyStats
    Especially "To address this problem, the secondary replica creates and maintains temporary statistics for secondary databases in tempdb. The suffix _readonly_database_statistic is appended to the name of temporary statistics to differentiate them from the permanent statistics that are persisted from the primary database."
    Got me thinking that if i give access to the tempdb in my replica from my linked server in my BI server, the problem might go away.
    But i cant test it right now because i already deleted the statistics on my primary. I'll follow-up this comment if the problem ever comes back.

  • Swen commented  ·   ·  Flag as inappropriate

    Had the same issue yesterday (25-02-2109) on SQL 2016 Always On AG. The secondary is used through linked server.

    The error was 'missing statistics'

    Removed the statistic on the specific table and column on the primary. Manually recreated the statistic.
    After that we were able to access the data again.

    This was a PRODUCTION reporting problem!

  • Anonymous commented  ·   ·  Flag as inappropriate

    Same problem here, running mssqlserver2016 version 13.0.5221.0. Can use the workaround but keeps comming back. To microsoft, can you provide us with a permanent solution, reporting is not working correct at this time.

  • Joe Hirsch commented  ·   ·  Flag as inappropriate

    Hello,

    I've sat down and worked with our SSIS developers and determined that this error is only happening when the a table is queries via a Linked Server using the four part naming convention. We do not see this error when the OPENQUERY function is used.
    Root cause is when the DB Startup command for the secondary database is delayed from creating the table column statistics on the secondary for an extended period of time. Can anyone from Microsoft describe, in great detail, what the workflow is for creating database table column statistics on a secondary database? Perhaps if we know each step in the workflow then we can determine why delaying the creation of them by DB STARTUP would prevent them from being in the system catalog.

  • Joe Hirsch commented  ·   ·  Flag as inappropriate

    Hello,

    I created the following script that runs on the secondary and has done a good job of detecting stats that are not in the system catalog for a specific database. It is rough looking but if you tweak it then it will get the job done.

    SET Transaction Isolation Level read uncommitted;
    SET NOCOUNT ON;

    Declare @objectid int,
    @statsid int,
    @ObjectName sysname,
    @StatsName sysname,
    @counter int = 0,
    @count int = 0;

    --Build the temp table for stats to check
    Create table ##BrokenStats (ObjectName sysname, StatsName sysname);
    Create table ##StatsAndTable ([object_id] int, [name] sysname, stats_id int, complete bit);

    insert into ##StatsAndTable
    select [object_id],
    [name],
    stats_id,
    complete=NULL
    from sys.stats
    where is_temporary = 0
    order by [object_id],stats_id;

    create clustered index cidx_StatsAndTable on ##StatsAndTable([object_id], [name], stats_id);

    SET @counter = (select count(*) from ##StatsAndTable); -- Total number of statistics that we need to check for the DB objects.

    WHILE @count < @counter
    BEGIN
    SET @objectid = (select top 1 [object_id] from ##StatsAndTable where complete is null); -- The DB Object that stats will be checked.
    DECLARE @statcount int = 0;
    DECLARE @statcounter int = (select count(*) from ##StatsAndTable where [object_id] = @objectid) -- Number of statistics for the DB object

    WHILE @statcount < @statcounter --Check to see if they are in the system catalog
    BEGIN
    SET @statsid = (select top 1 stats_id from ##StatsAndTable where [object_id] = @objectid and complete is null)
    IF (select [object_id] from sys.dm_db_stats_properties(@objectid,@statsid)) is null -- Null if the stats are not in the system catalog
    BEGIN
    SET @ObjectName = (select object_name(@objectid))
    SET @StatsName = (Select [name] from sys.stats where [object_id] = @objectid and stats_id = @statsid)
    insert into ##BrokenStats VALUES(@ObjectName,@StatsName)
    END
    SET @statcount = @statcount + 1
    UPDATE ##StatsAndTable SET complete = 1 where [object_id] = @objectid AND stats_id = @statsid; -- Mark the stats as checked for the object
    END


    SET @count = @count + 1;
    END
    IF (select count(*) from ##BrokenStats) <> 0
    BEGIN
    Execute msdb..sp_send_dbmail
    @recipients='',
    @subject='Stats are not in the System Catalog',
    @body='Please review the following statistics that are not in the system catalog.',
    @query='select * from ##BrokenStats';
    END
    DROP TABLE ##BrokenStats;
    DROP TABLE ##StatsAndTable;

  • Mikhail Makeenkov commented  ·   ·  Flag as inappropriate

    Hi Joe.
    We have the same issue in SQL Server 2012 SP3 and our database has 110 compatibility level.
    So I think problem is not in version gap.

  • Joe Hirsch commented  ·   ·  Flag as inappropriate

    This is happening with an AlwaysOn secondary database at the company that I work for. We are using the SQL Server 2014 SP3 database engine. The table contains 84 columns. The column statistics that are not replicating from the primary to the secondary are being auto created. I did notice tonight that the database is in compatibility mode for 2008. The error that is being thrown is Error 2767 the statistics could not be located in the system catalogs.
    I wonder if the versions gap between the database instance and the database is so great that there has been a change to an under the hood process along the way that applies to how statistics can be viewed in views such as sys.stats. Since the resource database would be upgraded and it contains the sys schema but the database is not upgraded then there has to be a disconnect within this process. Can anyone else confirm the compatibility version of their database compared to the version of the database instance?

  • Arsh commented  ·   ·  Flag as inappropriate

    Workaround mentioned above works, but the issue comes back time to time.

    Example:

    “Could not locate statistics '_WA_Sys_00000004_4A202281' in the system catalogs.”

    Fix:

    Go to the primary replica and run the following query to get the table and column that this statistic belongs to:

    USE [databaseName];

    GO

    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:

    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

  • Carlos Martinez. commented  ·   ·  Flag as inappropriate

    Same issue with SQL 2014 CU5 and asynchronous AlwaysOn replica. I'll try removing statistics from source, but agree cannot use this workaround in a 24x7 database that's why an AlwaysOn setup is intended. Please, we need a solution soon. If I can do anything to help, let me know.

  • Aldo Gonzalez commented  ·   ·  Flag as inappropriate

    Here's a workaround.
    SCENARIO: We have a linked server on a SQL Server 2008R2 server pointed at the secondary replica of a database on a two-node SQL Server 2014 SP2 AlwaysOn cluster. The AG is set to Synchronous commit. Users were getting the 2767 error mentioned in this Connect.
    WORKAROUND: Drop the automatically generated statistics in the error and create a statistics to replace it.

  • JOHN HEIMILLER commented  ·   ·  Flag as inappropriate

    I should also mention when I went to the primary and tried to drop the stats directly...I got the does-not-exist message...weird because they are there. I updated the stats (still had secondary select problem)...then was able to drop them....and select works.

  • JOHN HEIMILLER commented  ·   ·  Flag as inappropriate

    Happening to us too. User going thru Linked server to select from large table and gets error on stats. Will try dropping stats....but if the stats come back and the problem re-occurs, then thats an issue.

  • Javier Villegas commented  ·   ·  Flag as inappropriate

    This is still happening to me once in a while. it is quite annoying since we rely on the RO replica and the day we have this issue most of the processes fails because of this

Feedback and Knowledge Base