SQL Server Training | Tutorials | How to's

As DBA we Schedule everything to automate the system as possible as we can and at times there is a requirement to delete old files from the Server. There are various ways to DELETE OLD FILES FROM SERVER

1. Write a Script (VB / Power shell / BATCH) file to Delete Old LOG / Report files and schedule a task in Windows Task Scheduler
2. Write a Script (VB / Power shell / BATCH) file to Delete Old LOG / Report files and call this Script file from a SQL Server AGENT JOB
3. Create a SQL Server Maintenance Plan and delete old files.

Let’s take a quick look on this methods and there pros and cons

Solution 1 : Write a Script (VB / Power shell / BATCH) file to Delete Old LOG / Report files and schedule a task in Windows Task Scheduler

STEPS to Create a Windows Task Scheduler to Delete Old files

STEP 1  - Create a Script file using either VB / ACTIVE X / Power shell / Batch File which can delete X number of old files.

Sample VB Script to Delete old files from various locations and different number of old days, in this Script all files older than 35 days will be deleted from F:\Reports and all files older than 10 days will be deleted from G:\SQLBackup

Call fnDeleteOldFiles("F:\Reports", 35)
Call fnDeleteOldFiles("G:\SQLBackup", 10)

Function fnDeleteOldFiles(FolderPath, RetentionIndays) 
   Dim fso, f, fc, FileEntry
   Set fso = CreateObject("Scripting.FileSystemObject")
   If (fso.FolderExists(FolderPath)) Then
      Set f = fso.GetFolder(FolderPath) 
      Set fc = f.Files
      For Each FileEntry in fc
         If DateDiff("d", FileEntry.DateLastModified, Now) > RetentionIndays Then
            FileEntry.Delete(True) 
         End If
      Next
      Set FileEntry = Nothing
      Set fc = Nothing
      Set f = Nothing
   End if
   set fso = Nothing
End Function

Sample PowerShell Script to Delete old files older than 10 days

#----- defining  parameters -----#
#----- current date ----#
$Now = Get-Date 
#----- define amount of days,  All files older than these days will be Deleted----#
$Days = "10"
#----- define folder where files are located ----#
$TargetLocation = "G:\SQLBackup"
#----- define type of files ----#
$Extension = "*.log"
#----- define LastWriteTime parameter based on $Days ---#
$LastWrite = $Now.AddDays(-$Days)

#----- get files based on lastwrite filter and specified folder ---#
$Files = Get-Childitem $TargetLocation -Include $Extension -Recurse | Where {$_.LastWriteTime -le "$LastWrite"}

foreach ($File in $Files) 
    {
    if ($File -ne $NULL)
        {
        write-host "Deleting File $File" -ForegroundColor "DarkRed"
        Remove-Item $File.FullName | out-null
        }
    else
        {
        Write-Host "No more files to delete!" -foregroundcolor "Green"
        }
    }

Sample Batch (CMD) Script to Delete old files older than 10 days

@echo off
:: set Target Location 
set dump_path=G:\SQLBackups

:: set number of Days of files and folders to delete
set days_to_keep=10

:: remove files from %dump_path%
forfiles -p %dump_path% -m *.* -d -%days_to_keep% -c "cmd  /c del /q @path"

:: remove sub directories from %dump_path%
forfiles -p %dump_path% -d -%days_to_keep% -c "cmd /c IF @isdir == TRUE rd /S /Q @path"

STEP 2  - Create a repetitive Task in Windows Task Scheduler and call this Script file to delete old files.

Go to Task Scheduler, Start button >> Control Panel >> Administrative Tools>> Task Scheduler >> Action menu >> Create Basic Task >> Schedule, as per your requirement >> Locate a Script, which we created >> Finish

Solution 2 : Write a Script (VB / Power shell / BATCH) file to Delete Old LOG / Report files and call this Script file from a SQL Server AGENT JOB

STEP 1  - Create a Script file using either VB / ACTIVE X / Power shell / Batch File which can delete X number of old files, same as previous section

STEP 2  - Create a JOB inside SQL Server using SQL Server AGENT.

Solution 3 : Create a SQL Server Maintenance Plan and delete old files

 

I know this is very old topic and this is how I ended up in writing on this. I was working on a Reproving Services project where a  daily report files is Generated and stored locally on the Server and this Job / Schedule was working perfectly fine.

This Morning, the request didn’t received the file because the Job failed and Job failed because the there is no space for a new file creation.

I was shocked this is 500 GB drive, then how come a dedicated drive go out of space, when I dig into details, there are thousand of files exists on this Server as no file is being deleted since this system was set up.

When I reached to business they said, they no more need a month old files. So there is a lack of maintenance task to delete old files.

So I immediately implemented maintenance task (SQL Server Job / Schedule) to delete all report files older than 5 weeks.