How to get sql job failures alerts

[Ram4]

How to get sql job failures from cloud sql end(SQL Server) when it fail I should get email notification

[ms4446]

CloudSQL for SQL Server does not natively support sending SQL Server Agent job failure alerts via email or other methods. However, you can create a workaround using Google Cloud's Operations Suite, specifically Cloud Logging and Cloud Monitoring, to create alerts for job failures.

Here's a general approach:

1. Ensure SQL Server Agent job failures are logged: SQL Server Agent jobs log their output and errors. If your jobs are not currently configured to do this, you should adjust their settings to ensure that they are.

2. Access the logs in Cloud Logging: Once your SQL Server Agent jobs are properly logging their output and errors, these logs should be automatically collected by Cloud Logging in your Cloud SQL instance. You can view these logs in the Google Cloud Console by going to the Cloud Logging section and looking for your Cloud SQL instance logs.

3. Create a log-based metric: In Cloud Logging, you can create a log-based metric that counts the occurrence of specific log events, such as a SQL Server Agent job failure. This would involve creating a filter for log entries that match the text of a SQL Server Agent job failure message.

4. Create an alerting policy in Cloud Monitoring: Once you have a log-based metric that counts job failures, you can create an alerting policy in Cloud Monitoring that sends an alert when this metric exceeds a certain threshold. For instance, you could set up an alert to be sent whenever the count of job failures in the past hour is greater than zero.

5. Configure notification channels: When you create the alerting policy, you can specify one or more notification channels for sending the alert. These could include email, SMS, or other methods supported by Cloud Monitoring.

Please note that this is a general approach and the exact steps may vary depending on the specifics of your Cloud SQL and SQL Server Agent job setup. The exact text you should use to filter for SQL Server Agent job failures in the logs will depend on the error messages that your jobs emit when they fail. It's recommended to run some tests to determine what these error messages look like in your case.

For more details see https://cloud.google.com/blog/products/databases/cloud-sql-alerting

[Ram4]

Thanks for your reply

Need to know how to log the sql job failures

Ensure SQL Server Agent job failures are logged: SQL Server Agent jobs log their output and errors. If your jobs are not currently configured to do this, you should adjust their settings to ensure that they are

[ms4446]

SQL Server Agent jobs automatically write to the SQL Server Agent error log when they fail. You can use this information to create a notification system within Google Cloud.

Here are the steps to access the SQL Server Agent error log:

1. Connect to your SQL Server instance using SQL Server Management Studio (SSMS), Azure Data Studio, or a similar SQL Server client tool.

2. Expand the "SQL Server Agent" node in the Object Explorer.

3. Expand the "Error Logs" node under the SQL Server Agent node.

4. Right-click on the "Current" log file and select "View Agent Log". This should open the current SQL Server Agent log, where you can see information about job executions, including failures.

You can set up a SQL Server Agent job step to write a custom message to the SQL Server Agent error log when a job fails. Here's an example of how you might do this:

1. Open the properties for the job that you want to monitor.

2. For each job step, go to the "Advanced" tab.

3. In the "On failure action" drop-down, select "Quit the job reporting failure". This will cause the job to write an entry to the SQL Server Agent error log if the step fails.

4. Repeat for each step in the job.

Please note that while you can view the SQL Server Agent error log from within SQL Server Management Studio or Azure Data Studio, Google Cloud's Cloud SQL does not currently support direct access to these logs from outside of the SQL Server instance itself.

As such, to create a notification system based on these logs, you would need to create a SQL Server Agent job or use a tool like Azure Data Studio to regularly query the error log and write the results to a table within your database. You can then set up Google Cloud Monitoring to alert on changes to this table.

You can also write your own custom logging and notification system using SQL Server's built-in features. For instance, you could write a stored procedure that checks the job history for failures and sends an email using Database Mail if it finds any. You would then schedule this stored procedure to run regularly as a SQL Server Agent job. Note that setting up Database Mail requires sending mail through an SMTP server, which may require additional configuration.

[Ram4]

Hello,

Thank you for your response.

In Cloud SQL, only the Enterprise edition supports SQL Agent job logging, allowing you to log SQL job failures. , the Standard edition does not provide this feature.

Please let me know Cloud SQL standard edition supports SQL Agent job logging? or not

[ms4446]

SQL Server Agent is supported in Standard, Enterprise and Web Editions of SQL Server. It is not supported in Express Editions

[Ram4]

Thanks for your response

i'm talking about Cloudsql agent job failures logging feature enterprise vs standard

Can you please share to me if you have any documentation(enterprise vs standard)

[ms4446]

Please refer to Microsoft - Editions and supported features of SQL Server for differences between editions - https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql...

For CloudSQL Supported features and Unsupported features please refer to https://cloud.google.com/sql/docs/sqlserver/features

[tesh42]

If you expect all jobs to succeed and want to be alerted when any job failed or the number of failed jobs exceeded a threshold, then you can use `database/sqlserver/sql_agent/jobs` metric. From https://cloud.google.com/sql/docs/sqlserver/admin-api/metrics:

```

database/sqlserver/sql_agent/jobs SQL Server agent jobs
GAUGE, INT64, 1 cloudsql_database	Current number of SQL Server agent jobs on the instance. Sampled every 60 seconds. After sampling, data is not visible for up to 210 seconds. is_enabled: (BOOL) Indicates whether a job is enabled. last_run_outcome: Last outcome of a job run, one of [Fail, Succeed, Retry, Cancel, In progress, Unknown].

```

You can monitor the number of jobs and setup alerts in Cloud Monitoring: https://cloud.google.com/monitoring