Arsh

My feedback

  1. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Arsh shared this idea  · 
  2. 36 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    15 comments  ·  SQL Server » Bugs  ·  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…

    Arsh commented  · 

    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

  3. 1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  0 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Arsh shared this idea  · 

Feedback and Knowledge Base