MS SQL 2005 Maintenance Plan “Backup set will expire” not working

If you ran into this problem when you setup “backup set will expire” option in your maintenance plan and backup (*.bak) and transaction (*.trn) files don’t get deleted after the completion of the backup job. Then, most likely you didn’t setup a cleanup job for the maintenance plan. Microsoft SQL 2005 is a little bit different then MS SQL 2000. In MS SQL 2005, you will need to add the “Maintenance cleanup task” to your backup job.

Once you have added the “Maintenance cleanup task”, there should be a t-sql query that look like something below:

EXECUTE master.dbo.xp_delete_file 0,N’D:\Backups\MSSQL’,N’bak’,N’2009-05-01T13:05:09′,1