How to know that , Is it Maintenance Cleanup Task is working properly from maintenance Plan or not through T-SQL? I would like to say that i had configured &quot;Automatic DB Backup&quot; as well as &quot;Automatic DB Cleanup Task&quot; through &quot;Maintenance Plan&quot; in MS SQL Server 2012 on my Production Database. And that is working properly. I had set up my &quot;Maintenance Clean up&quot; task &quot;Older than 4 Weeks&quot;.
Using the following T-SQL Statement:-

use msdb
select * from
dbo.backupmediafamily;

I am able to view the backup history of my DB. That is working properly through maintenance plan schedule.
I setup Automatic Backup of 3 Databases through the &quot;Maintenance Plan&quot;, But I had configured Maintenance Cleanup Task to delete files older than 4 weeks. During the last month there are 90 (Backup) files . I configured this plan on 26-03-2015, so now my Maintenance Cleanup Task is working now and I also delete (backup files, .bak) from the specified location.

Now I am able to check out deleted backup file from specified location with Maintenance Plan. But after few days or few months I may be not able to collect exact deleted backup file records as well as not recognize exact days delete backup file records.
So, I want to know exact deleted (.bak) records from through T-SQL.
If you want to be able to view the exact backup history for longer time than those four weeks that you keep the backup files on disk, make sure that you set the cleanup task not to delete the backup history from the msdb database, you can then run the backup report from SSMS (right click database, select reports, standard reports, backup and restore events) or run the following to view the backup history: 

First create this function - I'll just have it in msdb - which is not a good idea.

create FUNCTION dbo.fc_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO


Then you can run the following

SELECT     
 sd.name AS database_name, 
 ISNULL(bs.backup_finish_date, 0) AS backup_date,
 ISNULL(CASE bs.[type] 
  WHEN 'D' THEN 'Full' 
  WHEN 'I' THEN 'Differential'
  WHEN 'L' THEN 'Transaction Log' 
  END, 'NEVER') as type ,
 bm.physical_device_name, 
 dbo.fc_FileExists(bm.physical_device_name) as DoesFileExists,
 bs.backup_size / 1024 / 1024 AS backup_size, 
 DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS duration
 FROM master.dbo.sysdatabases AS sd 
 LEFT OUTER JOIN msdb..backupset AS bs ON sd.name = bs.database_name 
 LEFT OUTER JOIN msdb..backupmediafamily AS bm ON bm.media_set_id =     bs.media_set_id
  GROUP BY 
   sd.name, 
   bm.physical_device_name, 
   bs.backup_size / 1024 / 1024, 
   DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date),
   bs.backup_finish_date,
   bs.[type],
   dbo.fc_FileExists(filename)
 order by bs.backup_finish_date desc