Scopri come risolvere l’errore 2767 in Microsoft SQL Server, che si verifica quando una query fallisce sulla replica secondaria, attraverso un processo automatizzato.
Se lavori con Microsoft SQL Server, potresti aver riscontrato l’errore 2767, che si verifica quando una query fallisce sulla replica secondaria. Questo errore può essere particolarmente problematico, poiché può interrompere il flusso di lavoro e causare ritardi significativi. Tuttavia, esiste una soluzione per mitigare questo problema: convertire le statistiche create automaticamente in statistiche create dall’utente.
La conversione delle statistiche può sembrare un compito arduo, soprattutto se si considera che deve essere eseguita per ogni statistica creata automaticamente. Tuttavia, l’automazione di questo processo può semplificare notevolmente il compito. Inoltre, se si mantiene attiva la creazione automatica di statistiche per il database, si potrebbe riscontrare nuovamente l’errore 2767 quando vengono create nuove statistiche automatiche nel database e si tenta di utilizzarle sulla replica secondaria.
Per aiutare ad automatizzare il processo, è possibile utilizzare uno script che esegue ciò che è stato suggerito nell’articolo originale per ogni statistica creata automaticamente. Questo script può essere eseguito come stored procedure nel tuo database per monitorare le nuove statistiche create automaticamente e automatizzare la loro conversione in statistiche create dall’utente.
È importante notare che, per attivare l’azione, è necessario impostare il parametro [@Whatif] su 0, altrimenti verranno solo segnalati i passaggi suggeriti. Di seguito è riportato il codice della stored procedure:
CREATE OR ALTER PROCEDURE MigrateAutoCreatedStatsToUserCreated (@WhatIf bit =1) as BAGIN /* Date: 2024-06-03 | V1.0 Authored by: Yochanan Rachamim Purpose: This procedure created to help with mitigation of error 2767 as described here: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/availability-groups/error-2767-query-secondary-replica-fails this will drop auto created statistics and recreate them as user created statistics. this script can be used as automated process that run on regular basis to prevent reoccurances of exception 2767 DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. */ SET NOCOUNT ON; DECLARE @iPrefix varchar(5) = 'User_'; SELECT --ss.object_id --,table_name = st.name --, stats_name = ss.name --,ss.stats_id --,sc.stats_column_id --,sc.column_id --,scol.name column_name DROP_COMMAND = 'DROP STATISTICS ['+ sschem.name +'].[' + st.name + '].['+ ss.name +']', CREATE_COMMAND = 'CREATE STATISTICS ['+ @iPrefix + ss.name +'] ON ['+ sschem.name +'].[' + st.name + ']('+ scol.name +')' INTO #StatsTemp FROM sys.stats ss JOIN sys.stats_columns sc ON ss.object_id=sc.object_id and ss.stats_id = sc.stats_id JOIN sys.tables st ON st.object_id = ss.object_id JOIN sys.schemas sschem ON st.schema_id = sschem.schema_id JOIN sys.columns scol ON st.object_id = scol.object_id and sc.column_id = scol.column_id WHERE ss.auto_created=1; DECLARE @t TABLE(DROP_COMMAND NVARCHAR(max), CREATE_COMMAND NVARCHAR(max)); DECLARE @cmd NVARCHAR(max); IF @WhatIf=1 RAISERROR('WhatIf command activated, no actual command will be executed',0,1) WITH NOWAIT; IF @WhatIf=1 RAISERROR('WhatIf mode is defaut, use @whatif=0 for actual execution',0,1) WITH NOWAIT; WHILE EXISTS(SELECT * FROM #StatsTemp) BEGIN DELETE TOP (1) FROM #StatsTemp OUTPUT deleted.* INTO @t; SELECT @cmd = DROP_COMMAND + '; '+ CREATE_COMMAND FROM @t; IF @WhatIf=0 exec(@cmd); RAISERROR(@cmd,0,1) WITH NOWAIT; END RAISERROR('Done',0,1) WITH NOWAIT;ENDGOSpero che troverai utile questa soluzione per risolvere l’errore 2767 in Microsoft SQL Server. Ricorda, la chiave è l’automazione del processo per garantire che il problema non si ripresenti in futuro.