SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Cleanup old text files on the errorlog directory

By Gerrit Mantel,

I have a lot of .txt files on the errorlog directory, coming from jobstep output. I want to delete all old .txt files on a regularly basis.

I noticed that a Maintenance Cleanup Task to clean Maintenance Plan text reports does not cleanup jobstep output text files. Apparantly the procedure xp_delete_file (used by this task) can only delete Maintenance Plan text files and not Jobstep output text files.

After puzzling a while I managed to write a small generic Powershell script that works in a jobstep. Of course the jobstep has to be of the type "PowerShell". I tested it on sql2008R2 and sql2014.

What does it do?

It deletes *.txt files older than 90 days on the errrlog directory of the default instance of the user where the job is running under. Mostly this is the SQL Agent service owner.

How to implement it?

Create a job. Create a  step with type PowerShell. Paste the code into the Command window. Adjust the $Days variable. Create a (weekly) schedule and you're done.

Always test new code first on a testenvironment!

Gerrit Mantel, LUMC, The Netherlands

Total article views: 421 | Views in the last 30 days: 1
Related Articles

SQL Error 53 when trying to delete system maintenance plan

Error on attempt to delete maintenance Plan.


output behavior

delete output into identity column


Maintenance Plan not deleting older .BAK Files

Maintenance Plan work fine but not deleting older .back files


The OUTPUT Clause for INSERT & DELETE Statements

In this article, I will provide a set of examples to show case the use of OUTPUT clause for INSERT a...


Maintenance clenup task not deleting bak and trn files

Maintenance clenup task not deleting bak and trn files