# Deleting other files during maintenance plan

I have an SQL Server 2012 maintenance plan which does the normal stuff (rebuild indexes and update statistics, back up DBs, deletes .bak files older than X days, etc). Everything is working fine with it, except one Maintenance Cleanup Task:

This should be deleting all .7z archives (equivalent to .zip or .rar) which get accidentally left in the nightly backups directory (despite our best efforts, we keep ending up with some people leaving these files on the server...), but we still have old .7z files present in subdirectories (e.g. E:\Backups\foo\bar.7z ) after the maintenance plan runs.

There are no errors in the SQL Server Logs.

I suspect this is because the "Delete files of the following types: Backup files" option is restricting the file types which can be deleted. I've also tried changing that to "Maintenance Plan text reports" with the 7z extension, with no change.

Is it possible to have a maintenance plan delete arbitrary (non-SQL Server) files during a Maintenance Cleanup Task? If so, any idea what I'm doing wrong?

As per MS here

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.

By using the Maintenance Cleanup task, a package can remove the backup files or maintenance plan reports on the specified server. The Maintenance Cleanup task includes an option to remove a specific file or remove a group of files in a folder. Optionally you can specify the extension of the files to delete.

I have not deleted any other extension files via maintenance plans and seems from above that a non sql file may not be deleted via Maint'ce plans:

What we use is a VB script and schedule it via SQL agent job:

Select type as :- Type ActiveX scipt when you create the step for below code in SQL agent job:

On Error Resume Next
Dim fso, folder, files, sFolder, sFolderTarget
Set fso = CreateObject("Scripting.FileSystemObject")

'location of the  files
sFolder = "B:\FolderA\ABC\"

Set folder = fso.GetFolder(sFolder)
Set files = folder.Files

'used for writing to textfile - generate report on files deleted
Const ForAppending = 8

'you need to create a folder named "scripts" for ease of file management &
'a file inside it named "LOG.txt" for delete activity logging
Set objFile = fso.OpenTextFile(sFolder & "\Report\LOG.txt", ForAppending)

objFile.Write "================================================================" & VBCRLF & VBCRLF
objFile.Write "                     FILE REPORT                " & VBCRLF
objFile.Write "                     DATE:  " &    FormatDateTime(Now(),1)   & "" & VBCRLF
objFile.Write "                     TIME:  " &    FormatDateTime(Now(),3)   & "" & VBCRLF & VBCRLF
objFile.Write "================================================================" & VBCRLF

'iterate thru each of the files in the respective folder
For Each itemFiles In files
'retrieve complete path of file for the DeleteFile method and to extract
'file extension using the GetExtensionName method
a=sFolder & itemFiles.Name

'retrieve file extension
b = fso.GetExtensionName(a)
'check if the file extension is abc(whatever is the extension of you're file
If uCase(b)="abc" Then

'check if the files are older than 1days
If DateDiff("d",itemFiles.DateCreated,Now()) >= 1 Then

'Delete any old files to cleanup folder
fso.DeleteFile a
objFile.WriteLine "FILE DELETED: " & a
End If
End If
Next

objFile.WriteLine "================================================================" & VBCRLF & VBCRLF

objFile.Close

Set objFile = Nothing
Set fso = Nothing
Set folder = Nothing
Set files = Nothing


Note* Above scripts work such that you need to create folder Report within you're base folder location and within that place a file LOG.txt which captures or logs the details of files deleted by the process. You can use this additional feature which we use to track, else modify the script as per needs!

Even though, @KASQLDBA has answered the question, below will supplement his answer from a different perspective :

• +1 For further enhancement to the answer by you and Ola's MP as always! Totally agree with you. :) May 28 '15 at 15:38