I have a 2 node AG setup (Not an FCI). I need to configure an alert to trigger a job based on a string found in the error log showing that the node is now the new primary.
The string I want to use is:

'primary_pending' to 'primary_normal'

This is what shows in the error log when the secondary node becomes primary. I want the alert to kick off a SQL job I created to automate our SSRS to use the new primary replica.

Below is the alert I setup, but it is not triggering properly. I do have the alert configured to execute the job when this alert is triggered.

When a failover happens, the job does not kick off. I'm guessing it may be due to the error number used. I found a blog post about using this, but can't seem to find it again. The error log does not display any error number in the line with this text, so I think that is the issue.

How can I get an alert to trigger based on text it finds in the error log, or is there a better way to do this?

I do have conditional logic to check for primary / secondary status in all my jobs, but I can't use that for this job as I want it to only execute 1x (when it first becomes primary).

Failover alert

1 Answer 1


You need to select <all databases> in the database name selection. The error you are checking for is not related to a specific database, so having a database specified is effectively filtering it so the alert doesn't care about it.

I just tested this using RAISERROR to log a message that included "'this' or 'that'" and it worked fine. At first I thought you may need to delimit the single quotes, but in my test I didn't and it worked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.