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 Monitor Addition Problems
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 credentials. This step helps diagnose connection issues, ensuring that both the server ...Self monitor Applications Manager using Real User Monitoring
We can monitor the Applications Manager using Real User Monitoring with a Java Script injection and this can be used to measure the Applications Manager's performance continuously. All you need is to install and setup the Real User Monitoring (RUM) ...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 ...Error message while performing database backup of Applications Manager
While performing a database backup operation of Applications Manager build with Pgsql backend, you may encounter the following error, especially when your database size has grown bigger with a large number of tables. pg_dump: WARNING: out of shared ...How to generate MS SQL Self-Help Tool report
Steps to generate MS SQL Self-Help tool report: Report by selecting existing host Navigate to Settings → Tools → Self Help Tools → DB Debug. Select "MS SQL" on the "Select Monitor" Drop-down. Select the issue reported MS SQL on the "Choose the Host" ...