Yesterday I found that the space on one of the servers was really low. The drive had a lot of SQL Server Database Backup files on it. From the file name it was clear that these backups were taken using a Maintenance Plan.
This server was running an instance of SQL Server 2005. There was a maintenance plan created to take backups to the local drive. The clean up task was also set to delete files older than 3 days. Still the drive had backup files older than a month!.
When I opened up the Maintenance Plan in SSMS, it looked “normal”.
Since the backup files were not getting deleted, something has to be wrong with the “Maintenance Cleanup Task”. All the options were set correctly in the Clean up task except for one. The File extension as highlighted in the below screen, was entered as “.bak”.
The problem here was that the file extension was entered as .bak instead of bak Yes the extra “.” was the difference. This is one of the common mistakes a DBA does especially if he/she had worked on SQL Server 2000. In the Maintenance Plans in SQL Server 2000, the file extension is always entered as .bak but not in SQL Server 2005 and above.
Now the root cause has been found, the change will be done tomorrow. One more page in the diary!