Failed to bring availability group 'agadven' online. The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online

Issue:- While configuring AlwaysOn in Azure Environment, sometime users get below error.

Problem:-

Disconnecting connection from NODE2...

Connecting to NODE2...

Disconnecting connection from NODE2...

Connecting to NODE1...

Msg 41131, Level 16, State 0, Line 70

Failed to bring availability group 'agadven' online.  The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again.

Msg 41152, Level 16, State 2, Line 70

Failed to create availability group 'agadven'.  The operation encountered SQL Server error 41131 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.

Disconnecting connection from NODE1...

Connecting to NODE2...

Msg 41044, Level 16, State 1, Line 82

Availability group name to ID map entry for availability group 'agadven' cannot be found in the Windows Server Failover Clustering (WSFC) store.  The availability group name may be incorrect, or the availability group may not exist in this Windows Server Failover Cluster.  Verify the availability group exists and that the availability group name is correct and then retry the operation.

Msg 41158, Level 16, State 3, Line 82

Failed to join local availability replica to availability group 'agadven'.  The operation encountered SQL Server error 41044 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command.

Disconnecting connection from NODE2...

Resolution:-

To resolve this issue, use one of the following methods.

Method 1: Use manual steps

if [NT AUTHORITY\SYSTEM] is not present in SQL server Logins Folder

Create a logon in SQL Server for the [NT AUTHORITY\SYSTEM] account on each SQL Server computer that hosts a replica in your availability group.

Grant the [NT AUTHORITY\SYSTEM] account the following server-level permissions:
Alter Any Availability Group
Connect SQL
View server state

Note Make sure that no other permissions are granted to the account.

Method 2: Use script

To create the [NT AUTHORITY\SYSTEM] account, run the following in a query window:

USE [master]
GO
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

To grant the permissions to the [NT AUTHORITY\SYSTEM] account, run the following in a query window:

GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]


After doing this, my issue resolved

Thanks for reading.

UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index.UPDATE STATISTICS is valid only when used with the STATS_STREAM option.

Error:-

If we have clustered column store index on a table and when we update statistics with traditional command like

UPDATE Statistics <TableName> (Statisticsname ) with FULLSCAN

-->The update statistics command gets fail with the below-highlighted message

Msg 35337, Level 16, State 1, Line 23

UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index.  UPDATE STATISTICS is valid only when used with the STATS_STREAM option.

use AdventureWork2014
go

CREATE CLUSTERED columnstore INDEX [PK_ErrorLog_ErrorLogID] ON [dbo].[Errorlog] WITH (DROP_EXISTING = OFF)

then Execute below command to retrieve

SELECT
OBJECT_NAME([sp].[object_id]) AS "Table",
[sp].[stats_id] AS "Statistic ID",
[s].[name] AS "Statistic",
[sp].[last_updated] AS "Last Updated",
[sp].[rows],
[sp].[rows_sampled],
[sp].[unfiltered_rows],
[sp].[modification_counter] AS "Modifications",
'UPDATE Statistics ' + OBJECT_NAME([sp].[object_id]) + ' ( ' + [s].[name] + ')' + ' with SAMPLE 30 percent'
FROM [sys].[stats] AS [s]
OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
WHERE [s].[object_id] = OBJECT_ID(N'[dbo].[Errorlog]');

go

execute the command 

UPDATE Statistics ErrorLog ( PK_ErrorLog_ErrorLogID) with SAMPLE 30 percent

you will get below error.

Msg 35337, Level 16, State 1, Line 23

UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index.  UPDATE STATISTICS is valid only when used with the STATS_STREAM option.

Resolution:-

In order to resolve this issue:-

We have to export that table statistics to a temp table. That table looks like this. It matches the output of DBCC SHOW_STATISTICS WITH HISTOGRAM.  


if exist drop # table

1.
drop table #stats_with_stream
go

2. Create a  #table #stats_with_stream

  CREATE TABLE #stats_with_stream
(
       stream VARBINARY(MAX) NOT NULL
       , rows INT NOT NULL
       , pages INT NOT NULL
);
go

3. Insert stream, rows and number of pages to  #stats_with_stream

INSERT INTO #stats_with_stream --SELECT * FROM #stats_with_stream
EXEC ('DBCC SHOW_STATISTICS (N''Adventureworks2014.dbo.[Errorlog]'',PK_ErrorLog_ErrorLogID )
  WITH STATS_STREAM,NO_INFOMSGS');

4. retrieve and check #stats_with_stream

select * from #stats_with_stream

5. The final step is to create the SQL that updates the statistics of our target table, and then execute it.

  DECLARE @sql NVARCHAR(MAX);
SET @sql = (SELECT 'UPDATE STATISTICS Adventureworks2014.dbo.Errorlog(PK_ErrorLog_ErrorLogID) WITH
STATS_STREAM = 0x' + CAST('' AS XML).value('xs:hexBinary(sql:column("stream"))',
'NVARCHAR(MAX)') FROM #stats_with_stream );


--PRINT (@sql);
EXEC (@sql);

Hence in this way, we can update statistics of that index which has clustered index.

PS:- For nonclustered column store index, we do not have to do anything.

Thanks for Reading..