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.
Schlosser, Ken R
To make matters even worse, you can't script out objects in SSMS 17 when you are connected to a SQL2019 database. So using the older version of SSMS so you can use source control won't work going forward. Please get TFS/GIT enabled in SSMS 18!!