Category Archives: AlwaysON

Standard

Posted by

Posted on

January 7, 2020

Posted under

AlwaysON

Comments

5 Comments

Generating Cluster.log for troubleshooting Always On Availability group failover

-> From command prompt, execute below command to generate cluster.log in C:\Windows\Cluster\Reports.

Cluster log /g

-> From command prompt, execute below command to generate a verbose cluster.log in location C:\Windows\Cluster\Reports.

Cluster log /g /Level:5

-> Cluster.log generated from command prompt will be using Coordinated Universal Time (UTC) and not local time. 

-> From powershell, execute below command to generate cluster.log of all nodes to one destination directory with local time stamp.

Get-ClusterLog -UseLocalTime -Destination .

-> We cannot specify level of details in Cluster.log if generated from powershell.

-> Sometimes, level of details while generating cluster.log can be important during troubleshooting. Always On Availability group can be a good example. This can be solved by changing “VerboseLogging” property of Availability Group resource in Cluster Administrator from default value 0 to 2. The resource should be made offline and online or wait till next failover to have this take effect and then generate the cluster log again.

Blog_70_1.PNG

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Standard

Posted by

Posted on

January 7, 2020

Posted under

AlwaysON

Comments

Leave a comment

Script to get Always On Availability group failover time from SQL Server Error log

-> Below script can be used to get Always On Availability group failover time from current SQL Server error log.

sp_readerrorlog 0,1,'The state of the local availability replica','JBAG'

From the above command,

sp_readerrorlog is the command.
0 refers to current SQL Server error log file. Increment this value to check old logs.
1 refers to SQL Server error log.
“The state of the local availability replica“ refers to search string 1.
“JBAG” refers to search string 2. I have used the name of availability group for which I am checking the failover time.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Standard

Posted by

Posted on

July 10, 2019

Posted under

AlwaysON, Core, Tsql

Comments

Leave a comment

Availability Group – SQL Server Agent Job to run only on Always On Primary Replica

-> I had a requirement to execute jobs only in primary replica.

-> To all jobs that should only be executed on Always On primary replica. I added 1 step as the first step of the job and another step as last step of that job.

-> 1st step of these jobs should execute below TSQL Query,

if (select
ars.role_desc
from sys.dm_hadr_availability_replica_states ars
inner join sys.availability_groups ag
on ars.group_id = ag.group_id
where ag.name = '<AGNAME>'
and ars.is_local = 1) = 'PRIMARY'
begin
Print 'This is Primary Replica'
end
else
begin
RAISERROR('This job should not run in Secondary',16,1)
end
-> For 1st step,

On success action : Go to the next step
On failure action: Go to step: [n] Last_Step ( It should basically go to the last step)

-> Last step of the job should execute the below TSQL,

print 'Job_Completion'
-> For Last step,

On success action : Quit the job reporting Success
On failure action: Quit the job reporting failure

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.