Export (0) Print
Expand All

smart_admin.fn_get_health_status

SQL Server 2014

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 Windows Azure is supported under the Smart Admin umbrella. So the errors returned are related to SQL Server Managed Backup to Windows Azure.

Applies to: SQL Server (SQL Server 2014 through current version).

Topic link icon Transact-SQL Syntax Conventions

smart_admin.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 Name

Data Type

Description

number_of_storage_connectivity_errors

int

Number of connection errors when the program connects to the Windows 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 Windows Azure.

number_of_retention_loops

int

The 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 smart_admin.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 smart_admin.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 smart_admin.fn_get_health_status(@startofweek, @endofweek)
    

Community Additions

ADD
Show:
© 2014 Microsoft