managed_backup.fn_get_health_status (Transact-SQL)

Applies to: SQL Server 2016 (13.x) and later versions

Returns a table of 0, one or more rows of aggregated count of the errors reported by Extended Events for a specified period of time.

The function is used to report health status of services under Smart Admin. Currently SQL Server managed backup to Microsoft Azure is supported under the Smart Admin umbrella. So the errors returned are related to SQL Server managed backup to Microsoft Azure.

Transact-SQL syntax conventions

Syntax

managed_backup.fn_get_health_status([@begin_time = ] 'time_1' , [ @end_time = ] 'time_2')  

Arguments

[@begin_time]
The start of the time period from which the aggregated count of errors is calculated. The @begin_time parameter is DATETIME. The default value is NULL. When the value is NULL the function will process events reported as early as 30 minutes before current time.

[ @end_time]
The end of the time period from which the aggregated count of errors is calculated. The @end_time parameter is DATETIME with a default value of NULL. When the value is NULL the function will process extended events as up to the current time.

Table Returned

Column Name	Data Type	Description
number_of_storage_connectivity_errors	int	Number of connection errors when the program connects to the Azure storage account.
number_of_sql_errors	int	Number of errors returned when the program connects to SQL Server Engine.
number_of_invalid_credential_errors	int	Number of errors returned when the program tries to authenticate using SQL Credentials.
number_of_other_errors	int	Number of errors in other categories besides connectivity, SQL, or credential.
number_of_corrupted_or_deleted_backups	int	Number of deleted or corrupted backup files.
number_of_backup_loops	int	The number of times backup agent scans all the databases configured with SQL Server managed backup to Microsoft Azure.
number_of_retention_loops	int	The number of times the databases are scanned to assess set retention period.

Best Practices

These aggregated counts can be used to monitor system health. For example, if the number_ of_retention_loops column is 0 in 30 minutes, it is possible that the retention management is taking long time or even not working correctly. Non-zero error columns may indicate problems and Extended events logs should be checked to learn of the any problems. Alternately, use the stored procedure managed_backup.sp_get_backup_diagnostics to get a list of Extended events to find the details of the error.

Security

Permissions

Requires SELECT permissions on the function.

Examples

• The following example returns aggregated error counts for the last 30 minutes from the time it was executed.

  SELECT *  
  FROM managed_backup.fn_get_health_status(NULL, NULL)  
  
  

• The following example returns the aggregated error counts for the current week:

  Use msdb  
  Go  
  DECLARE @startofweek datetime  
  DECLARE @endofweek datetime  
  SET @startofweek = DATEADD(Day, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)   
  SET @endofweek = DATEADD(Day, 7-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)  
  SELECT *  
  FROM managed_backup.fn_get_health_status(@startofweek, @endofweek)