SCRIPT: How to check PRIMARY node and if it is SECONDARY node, stop job itself?

 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 = 'YourAvailabilityGroupName' -- Availability group name
    AND ars.is_local = 1) = 'PRIMARY'
BEGIN
    PRINT 'PRIMARY'
END
ELSE
BEGIN
    PRINT 'SECONDARY'
       DECLARE @JobID uniqueidentifier
    SELECT @JobID = $(ESCAPE_NONE(JOBID));
    EXEC msdb.dbo.sp_stop_job @job_id = @JobID;
END

 

Comments