TechNet
Export (0) Print
Expand All

managed_backup.fn_get_health_status (Transact-SQL)

 

Updated: June 10, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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.

Topic link icon Transact-SQL Syntax Conventions

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

[@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.

Column NameData TypeDescription
number_of_storage_connectivity_errorsintNumber of connection errors when the program connects to the Windows Azure storage account.
number_of_sql_errorsintNumber of errors returned when the program connects to SQL Server Engine.
number_of_invalid_credential_errorsintNumber of errors returned when the program tries to authenticate using SQL Credentials.
number_of_other_errorsintNumber of errors in other categories besides connectivity, SQL, or credential.
number_of_corrupted_or_deleted_backupsintNumber of deleted or corrupted backup files.
number_of_backup_loopsintThe number of times backup agent scans all the databases configured with SQL Server Managed Backup to Microsoft Azure.
number_of_retention_loopsintThe number of times the databases are scanned to assess set retention period.

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.

Permissions

Requires SELECT permissions on the function.

  • 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)  
    
    
    

Community Additions

ADD
Show:
© 2016 Microsoft