One DBA's Ongoing Search for Clarity in the Middle of Nowhere


Yet Another Andy Writing About SQL Server

Wednesday, April 15, 2015

Deleting Files Older than X Hours with PowerShell

(aka "OMG I can't believe I am actually finally writing a #PowerShell blog post").


I currently have a situation at a client where we are running a server-side trace that is generating 6GB-7GB of trc files per day while we are watching to see what might be causing a server issue.

I need to keep enough trace files that if something happens late in the day or overnight we can run it down, but not so many as to have 6GB+ of files all of the time.

In the past for file cleanups I have relied on the ForFiles command (modified from the one used by Ola Hallengren in his Output File Cleanup job):

cmd /q /c "For /F "tokens=1 delims=" %v In ('ForFiles /P "$(ESCAPE_SQUOTE(SQLLOGDIR))" /m *_*_*_*.txt /d -30 2^>^&1') do if EXIST "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v echo del "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v& del "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v"
The problem in this case is that ForFiles takes a parameter in *days* (/d) and in my case I really want to delete files older than 18 *hours*.

Looking for another solution I figured there had to be a PowerShell solution (Remember - #YouCanDoAnythingWithPowerShell) and after some digging I found someone using the Get-ChildItem and Remove-Item cmdlets to do pretty much what I was looking for:

Get-ChildItem $env:temp/myfiles | where {$_.Lastwritetime -lt (date).addminutes(-15)} | remove-item

This sample would remove anything from temp\myfiles older than 15 minutes.  By hardcoding the -path parameter rather than relying on the $env variable and changing addminutes to addhours I was able to accomplish my goal:

Get-ChildItem -path D:\AndyG\Traces | where {$_.Lastwritetime -lt (date).addhours(-18)} | remove-item
 This command "gets" each item in the given path with a modification date-time older than 18 hours and then removes/deletes them from the folder .

After testing this in a PS window, I tried it as a PowerShell job step in a SQL Server Agent job and it worked!  (Of course, the account running the job step needs to have the necessary permissions on the D:\AndyG\Traces folder to perform the item deletes).

Moving forward, I may abandon the ForFile idea in favor of this whenever possible - it is simpler and PowerShell seems to be more and more what makes the world go around - hope this helps!

No comments:

Post a Comment