Task Scheduler for Windows Server 2003

Task Scheduler lets you automate tasks, and if you are lucky enough to have a Windows Server 2003 operating system, you get a simple to use, simple to understand wizard which helps you set it up.  

Woe betide you if you use Windows Server 2008 though… 

I'll do a write-up on that one when I get a chance. 

Click on /Start

/Control Panel

/ Scheduled Tasks

/Add Scheduled Task

And up pops the wizard.



 

Like it says, click on Next.



It will show you a list of programs that you may or may not want to run.  Scroll down the list and discover that the one you want isn’t there.  Now click on Browse.

I put my script in a folder which I call C:\Scripts.  Cunning, eh?  No-one will ever think of looking there.  Select the script and click on Open

 You can give your scheduled task a name, but it cleverly assumes that you will want to call it by the name of the program or script you selected.  Tell it how often you want to run this. 

And what time and what day you want to run it.

Then put in the name and password of a user who has the rights to do stuff.  You could enter your own name, but that will cause your successor a problem when you leave the company and everything stops working.  You might think that once you have gone you no longer care, but you never know if you will end up applying for a job to someone who remembers you. 

Hooray!  You’ve finished!  Easy wasn’t it?  One last thing to check – click the advanced options box and then Finish. 

This is the box you get if you want to edit your job, or if you set things up without the wizard.  It isn’t particularly advanced at all, if you ask me – the first two tabs just cover the ground that the wizard led you through.  But I think it’s worth stopping a task if it hasn’t succeeded after an hour, or whatever you think is a reasonable time, depending on what you want to do.  If it hasn’t succeeded after running for an hour, it probably isn’t going to no matter how long you leave it. 

Hope you find this useful.  I'll let you know when I've done the 2008 version. 

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.