It is common in most environments to have maintenance plans used to perform regular maintenance tasks like Full, Differential and Log Backups, Rebuild Indexes, Statistics Update and Integrity Check.
Backups are considered one of the most important task and proper care needs to be taken while creating the maintenance plans for performing the database backups in SQL Server. There are many options available while creating the backup maintenance plan and need to choose options correctly to avoid any failures or problems in future.
Although there can be various problems related to configuring backup maintenance plans, one of the small but difficult one to identify for many DBA’s seem to that they enable the task “Maintenance Cleanup Task” to delete old backups older than 3 days or so and they later identify the old backups are not getting deleted. The backup job will run successfully and does not report any errors in this case, the only way DBA’s identify the issue mostly is when the disk drive where backups are stored gets full or has grown to large size and alerts are raised.
Coming to the solution of this problem is mostly due to incorrect values provided to the “Maintenance Cleanup Task”. In the maintenance cleanup task, verify the option “File extension” and see if the extension of the backup to delete is correct, for example if backup task is creating the backups with extension .bak, make sure “bak” is mentioned, if for transaction log backups, extension is .trn, then make sure that the option provided is “trn”. It is important to note to use “bak”(without quotes) instead of “.bak” as using .bak will not delete the old backup files. So, make sure there is no .(Dot) infront of the extension bak or trn.
This is applicable on below versions of SQL Server.
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.