How is Backup Age calculated for MS SQL DB monitor?
Backup Age displays the number of hours passed since the backup operation was completed. In simple terms, it is the difference between the current time and the time at which backup was performed.
Backup Age (hours) = Current Time - Backup Time
By default, we collect the details of the backup taken in the last 7 days. This can be changed by specifying the number of days for the textbox present in 'Collect SQL Database Backup Details' option under Admin -> Performance Polling -> MS SQL -> Metric Name -> Backup.
For example, the following query is used to calculate Backup Details (under Backup/Restore tab) for the last 100 days (specified using the above option) in MS SQL DB monitor:
SELECT server_name, BS.database_name, backup_start_date, backup_finish_date, expiration_date, BS.recovery_model, is_damaged,CAST((CAST(DATEDIFF(s,backup_start_date,backup_finish_date) AS int))/3600 AS varchar) + ' hours, ' + CAST((CAST(DATEDIFF(s,backup_start_date,backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '+ CAST((CAST(DATEDIFF(s,backup_start_date,backup_finish_date) AS int))%60 AS varchar)+ ' seconds' AS 'TotalTime', BS.type AS backup_type, backup_size, logical_device_name, physical_device_name, BS.name AS backupset_name,description,DATEDIFF(hh, backup_finish_date,GETDATE()) AS Backup_Age FROM (SELECT M.database_name,M.type,max(M.backup_finish_date) AS maxtime FROM msdb.dbo.backupset AS M GROUP BY M.database_name,M.type) AS maxvalue ,msdb.dbo.backupset BS INNER JOIN msdb.dbo.backupmediafamily ON BS.media_set_id = msdb.dbo.backupmediafamily.media_set_id INNER JOIN sys.databases db ON db.name=BS.database_name WHERE (CONVERT(datetime, BS.backup_start_date, 102) >= GETDATE() -100) AND backup_finish_date=maxvalue.maxtime AND BS.type=maxvalue.type AND BS.database_name=maxvalue.database_name ORDER BY backup_finish_date desc
Note:
- In addition to the above option, there is an option Delete backup entries from Applications Manager when its database is removed, enabling which will delete the backup entries when its corresponding database is deleted from its SQL Server instance.
- If enabled, it will delete the backup entries when the backup is not taken for more than the number of days mentioned in the Store SQL Database Backup Details option.
- By default, it will delete the backup entries when the backup is not taken for more than 7 days.
New to ADSelfService Plus?
Related Articles
Troubleshooting MS SQL login failure
Problem: The following error message appears in MS SQL monitor during login: "Login Failed. The login is from an untrusted domain and cannot be used with Windows authentication" Solution: Verify if the password of the SQL account (domain\username) ...Troubleshooting MS SQL Monitor Addition Problems
Please refer to the Prerequisites for MS SQL Database Server before adding a monitor. 1. Ensure that the SQL Server is running and accessible. Try connecting to the SQL Server instance via SQL Server Management Studio or DBVisualizer using the same ...How do I migrate the MS SQL DB used by Applications Manager to another MS SQL server?
Follow the steps given below to migrate MS SQL database of Applications Manager to another MS SQL server: 1. Shutdown Applications Manager completely before the database migration. a. Stop Applications Manager service. b. Shutdown Applications ...Why are few database metrics in the Database tab showing empty values in MS SQL monitor?
Why are few database metrics in Database tab showing empty values in MS SQL monitor? Before Applications Manager version 13910, We were showing high-level metrics for MS SQL monitor's Database usage from SQL server performance counters. But from ...Why are thresholds and alerts not working in Database tab metrics of MS SQL monitor?
In Applications Manager's MS SQL monitor, metrics under Database tab are collected once in an hour by default. Therefore, under Admin -> Performance polling -> MS SQL -> Database Metrics, the option 'Collect SQL Database metrics once in an hour' is ...