Maintenance Plan Log Files Won't Die

When you run a maintenance plan in SQL Server 2005 – to take backups or databases and transaction logs, to check integrity or reorganise indexes – it creates a log of all the steps it has gone through.  If you have no life, you can read these logs every day.  But the chances are that you never will, unless you need to investigate a problem. 

The maintenance plans in SQL Server 2005 onwards have a Maintenance Cleanup Task, which lets you clear out old backups and old log files.  The backups will fill your disk pretty quickly if you don’t clear them out, but the log files only take up a couple of hundred kilobytes so on most servers it’s not a big deal.  But I like to keep things tidy, so I’ve recently gone through my servers (I have about 30) adding a cleanup task to get rid of the oldest ones every week. 

Here’s my Cleanup task for old backups:

The trick here is that the file extension must be “bak” and NOT “.bak” – put in that dot, and it won’t work.  Leave it out and it works beautifully, so thank you Mr Gates. 

Now - here’s my Cleanup task for old log files

Notice that I am deleting text reports rather than backup files, and the file extension is “txt” not “bak”.  Apart from that it is pretty much the same.  Except – sorry Mr Gates – it doesn’t always work. 

The maintenance plan runs, and reports success.  Here’s the underlying SQL (just press the View T_SQL button):

EXECUTE master.dbo.xp_delete_file

      0,

N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\',

      N'.txt',

      N'2010-10-14T14:25:31',

      1

     

Run the code, and it tells you:

Command(s) completed successfully.

Very reassuring.  Except that sometimes the old log files remain in their directory, smiling up at you in disdain.  Do your worst, they seem to say, see if we care. 

Google has lots of entries from people struggling to get the maintenance plan to work.  Most of them are adding “.bak” in the file extension.  Take out the dot and the problem goes away.  Pfft – easy peasy.  But that doesn’t help with text files. 

Let’s consult Microsoft – and see what the Documentation of xp_delete_file says.  Here we are:

http://connect.microsoft.com/SQLServer/feedback/details/124708/documentation-of-xp-delete-file

It’s super-helpful – I’ll quote it in its entirety:

Posted by Microsoft on 23/09/2005 at 18:28

Thank you for contacting us about this. I have checked with the feature owner. This extended stored procedure is undocumented by design.

Great – thanks Bill!  That’s a great bit of design.  I must remember that one when my manager asks me to document my code. 

But Google is my friend.  After a lot of searching I found another entry:

http://support.microsoft.com/kb/938085

This says it doesn't work on files where the first line of the report reads “NEW COMPONENT OUTPUT”.  And why, pray, does it say this in line 1?  Because SQL Server tells it to, of course.  Delete this, and it works!  So all I need to do is write a script to go through all my daily log files, deleting the words “NEW COMPONENT OUTPUT” from the first line, and run my maintenance plan clean up task again. 

Let me be fair to Microsoft – they have fixed it now.  There is a hotfix for this in 2005 sp2 (http://support.microsoft.com/kb/936305/), and it works in sp3 and SQL Server 2008. 

Serves me right for using software that can’t be migrated to the latest version!

OK, let us deploy the alternative cat-skinning apparatus.  Ever heard of Forfiles?  It’s a batch command for doing things to files.  More info here:

http://technet.microsoft.com/en-us/library/cc753551(WS.10).aspx

So what I do is set up a batch file in c:\scripts on my server called something like Delete_Old_Log_Files.bat, and set up a scheduled task to run it once a week . 

First step is to navigate to the location where the text files live.  If my log files lived somewhere like D:\Logs, I could use the path option in Forfiles: -p D:\Logs.  But sadly Forfiles doesn’t like spaces in directory names, and of course I have spaces in my directory names (and so do you, probably, if you use SQL Server’s defaults).    

I could try –p d:\ -s, which tells it to work through all the subdirectories on Drive D, deleting old text files like Genghis Khan sweeping over the steppes, and slaughtering all in his path.  Call me namby pamby if you must, but I’m a bit scared of the Mongolian approach to database administration. 

So I put this in my batch file:

cd\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

Then the forfiles command itself:

forfiles -m *.txt  -d -28  -c "cmd /C del @FILE"

-m is the search Mask – it says that I am interested in all the files with a txt suffix. 

-d is the number of Days before the current date – so I’m saying find me the files more than four weeks old. 

-c says run the Command in quotes (to delete those selected elderly txt files)

The whole batch file looks like this:

rem This batch file is set to run weekly

rem  It removes SQL Server log files >28 days old

rem Jack Whittaker 14/04/2010

rem navigate to the right location

c:

cd\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

rem delete all text files > 28 days old

forfiles -m *.txt  -d -28  -c "cmd /C del @FILE"

So if you were wondering why your log files were staying in place, you now know why, and what to do about it.  I hope this has helped.