How to backup and shrink Transaction log automatically?

[Digitalcandy]

Every Friday I am having to manually backup and shrink the transaction log. How can I do this automatically on Sunday night at 12AM?

When I do it manually on Fridays I use SQL querry analyzer and use the following commands;


1st command I run in Querry analyzer;

BACKUP LOG MyDatabase WITH TRUNCATE_ONLY



2nd command after the first is ran;

DBCC SHRINKFILE (MyDatabase_Log)

 

[JayKusch]

Put it into a Job. Open Enterprise Manager and drill down to "Management".."SQL Server Agent".."Jobs". On the Jobs tab, right-click and choose "New Job...".

The new job screen opens up. On the General tab, add a Job Name and maybe a little Description.

Go to the "Steps" tab and then click on "New". This will put you on the General tab of the step. This is where we will put the first command. Put an name in the Step Name like "Backup Log" and then paste the code ... BACKUP LOG MyDatabase WITH TRUNCATE_ONLY.

Now Click on the "Advanced" tab of the step. You have the option in each step on how to proceed when the step finishs correctly or fails. What we want it to do is go to the next step, Shrink Log, if the job finishs correclty and we want it to quit if this step, Backup Log, fails so ... The defaults are just fine. Click OK ... Step One DONE!

Step 2 repeates like step 1 except we name this step "Shrink Log" and we paste the code of
DBCC SHRINKFILE (MyDatabase_Log). In the Advanced tab we want to set the success step option to "quit the job reporting success" and we want to set the failed step option to be set to "quit the job reporting failure". Click OK ... Step 2 DONE!

Now go to the Schedule tab of the job and hit the "New Schedule" button. Give the sched a name, then hit the "Change" button. In here you can set the exact schedule you want the job to run.

You are now ready to roll. You can even right-click on the new job that is created and run it right now, if you wish, just to test it.

Enjoy!

Thanks

J. Kusch