SQL Server Maintenance Plan Cleanup Task

Question

I am using a SQL-Server 2012 (SP2) on a WinServer 2012 R2. I created a maintenance plan that makes a backup of my databases and deletes report files and backup files that are older than 2 days.

My problem is that the maintenance plan doesn't delete files which are older than 2 days. these files are also not deleted after 5 or 6 days. The backups are verified and compressed.

I have rights on the directory (agent is running under LocalSystem) and in the task properties under "file extension" I tried to set the name without and with a dot, e.g. ".bak", "bak" and "*.bak" but nothing works. I changed the order of the task and nothing works.

But I always get success messages. So what is here wrong?

Answer 1

Just to clarify, are you trying to run a script (i.e., .bat) to handle some of this through SQLCMD, and calling that script through Task Scheduler? From your question, it sounds like you've set the Maintenance Plan up in SQL Server, but are trying to setup a "Task" to call it (Task Scheduler), rather than a "Job" (SQL Agent), my apologies if this is not the case.

It will be a lot tidier to do this through the SQL Agent (the SQL Server internal equivalent of Task Scheduler). Once you have a Maintenance Paln you can test through the Management Studio Interface, it's a breeze to add the Agent Job.

If you are running a Task Scheduler Task to call a script, it is likely you're getting "success" because the Task started your script, but the script isn't setup to handle the returns from the SQLCMD call properly.

Answer 2

I have tried to solve it in different ways and there has been no way, the fastest I have found is to use a .bat that deletes them and schedule a windows task that does it every night.

This bat clears from the last 3 days :

FORFILES /P "C:\Directorio" /S /D -3 /c "CMD /c DEL /Q @PATH"