SQL Server 2005 RTM Maintenance Cleanup FAIL

A recent question on AskSSC reminded me of a little issue affecting SQL Server 2005’s maintenance plans; specifically, the Maintenance Cleanup task.

Those of you (un)lucky enough to still be running SQL 2005 RTM may well be aware of this, but for those of you who have yet to migrate up from SQL 2000 (and you are out there, I know this), or who can’t remember that far back (after all, SQL 2008 R2 has been out for a while), I thought I would share this little titbit.

Consider the following:

• The Backup task allows you to put database backups into a directory per database
• The Maintenance Cleanup task in SQL 2005 RTM does not check subdirectories for old backups.

So, the backups continue, and after a few days, your disk space usage graph looks like:

Free disk space for a SQL 2005 RTM server - D: drive (blue) shows % free space on the backup drive

Oops.

A quick trawl of the boards recommended applying service packs & other upgrades.  For various reasons this wasn’t an option (no downtime could be scheduled), so a Elastoplast had to be applied over this broken leg.

So.  If you find yourself in this sort of situation, you could try using the following VBScript:

Option Explicit
'Delete all SQL Server backup files more than 2 days old

Dim oFS, oSQLBackupFol, oFol, oFil
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oSQLBackupFol = oFS.GetFolder("D:\SQL Server Backups")   'Change this as appropriate

For Each oFol IN oSQLBackupFol.SubFolders
      For Each oFil in oFol.Files
           If oFil.DateCreated < Now-2 Then    'Change this as appropriate
                  'Every file - want to check the name?  then here's the place to do it.
                  oFil.Delete
            End If
      Next
Next

Save that as a .vbs file and set it to run as a Windows scheduled task. Or set up a SSIS job that runs the above.

caveat emptor: This is worth what you paid, and will be supported accordingly (ie not at all).  Note that it’ll delete every file that’s old enough in every first level sub-directory of the directory specified.  If you want to be cleverer or more paranoid, then sort it out yourself.