2

I have created a maintenance plan in SQL Server 2008 to delete transaction logs backups older than 3 days. Every day I look in the folder designated and the logs are still there. The job states it has run but nothing is removed. Job schedule is recurring with a daily frequency and the status is enabled.

If I copy the Transact-SQL and run it manually, it appears to clear space on the drive even though I don't see the logs removed up to the last 3 days. The transaction logs are saved to a folder on the target drive not the root itself.

Database recovery model is full and this is required due to the nature of the application that run against the SQL Server box. I have tried removing the maintenance plan and recreating it but I'm getting the same results (nothing). I also checked to see if the SQL Server agent isn't tied up on any job.

What am I doing wrong?

Improve this question
7
  • I just set recovery to simple, truncate the logs, set back to full. That clears out the transaction logs. It's easily scriptable.
    – Nathan C
    Sep 3, 2014 at 13:02
  • Nathan - If I do this in production, will this have any impact on services or user experience?
    – Jericho
    Sep 3, 2014 at 13:29
  • I would advise doing this when there's not a lot of traffic to your SQL server since it's fairly I/O intensive and could lock the database for a short period if it's a big one.
    – Nathan C
    Sep 3, 2014 at 13:33
  • 1
    I have never seen an application drive the recovery model of the database. Why does it require this?
    – user507
    Sep 3, 2014 at 21:15
  • 2
    Setting the recovery model to simple, truncating the log & back to full is almost always exactly the wrong approach to take. Among other things it breaks the backup chain. If you do this on a regular basis you may as well set your database to simple and be done with it.
    – Kenneth Fisher
    Sep 3, 2014 at 21:32

3 Answers 3

Reset to default
4

You can use Ola Hallengren's SQL Server Backup Solution.

Especially the CleanupTime will help you to delete the old backups (FULL / Diff or Transaction log backups).

Specify the time, in hours, after which the backup files are deleted.

Improve this answer
0
3

I suspect that there is some confusion about terminology here.

Maintenance plans do not delete transaction logs. Maintenance plans delete transaction log backups.

Transaction log files are required for the operation of a SQL Server database. Every SQL Server database has at least one transaction log file. By convention, these files are given a "LDF" extension. SQL Server log files do not behave like Oracle's redo logs. You can't just copy a log file and/or delete it.

In your configuration (FULL), when the maintenance plan runs it should be configured to execute a transaction log backup command. This (more or less) makes a copy of the information in the transaction log file and places that information in a separate file called a transaction log backup. The transaction log backup command then marks the relevent space in transaction log file as free for use (again) by SQL Server. If this "marking of space" does not occur, the transaction log will eventually fill up. (Things work differently if the database is running in SIMPLE mode, but you aren't doing that.)

With the FULL model, if the transaction log backup commands do not execute "often enough", the transaction log file can fill up. When that happens, depending on your exact configuration, the transaction log will either be automatically grown or updates to the database will start to fail. "often enough" is determined by how much space you have available, how big your files are, the rate of change of data in the database, the exact nature of how you do updates, your point-in-time recovery requirements, etc. Note that you can schedule transaction log backups in a highly flexible way: once a day, once an hour or once a minute or once every few minutes. You can even run transaction log backups more (or less) frequently during busy periods.

The next time the maintenance plan runs, it will do the same thing as it did the last time but it will vary the name of the transaction log backup file (using a timestamp) so the earlier file is not overwritten. The maintenance plan runs the transaction log backup command based on the schedule that you configure. As the minutes and hours and days pass, an ever-growing set of transaction log backup files is created. At some point, those files need to be trimmed back by deleting them or moving them to somewhere else. If you are running some sort of log-shipping scheme, it's possible that you would be moving the transaction log backup files to some other safe location.

The key concepts here are that the data is copied from the transaction log file to a separate transaction log backup file. These transaction log backup files should be stored for an appropriate period of time. A maintenance plan can be configured to automatically delete those files, you you can delete them manually or by some other scheduled method.

Generally, most sites keep things as simple as possible and that "appropriate period of time" would be until your next full backup. For example, if you are doing a full backup every Sunday, you will need to keep at least seven days of transaction log backups. (I'd keep eight days, I'd like a little bit of overlap just to be safe). If you only keep three days of transaction log backup files, you will not be able to fully restore your data once you get past Wednesday. In the simplest FULL strategy, in order to restore a database you need the last full database backup and all of the transaction log backups from the time the full database backup was taken until the time that you need to restore to. If you are taking full database backups on Sunday with a three-day lifetime on transaction log backup files, when it gets to be Friday you will be boned since the maintenance plan has deleted the transaction log backup files from Monday and Tuesday. You will only be able to restore the database using whatever was in the last full backup. I have seen people fall into that trap.

I'll close by saying that any person who is new to the way that SQL Server backup works should experiment/test restoring their database onto a spare machine, just to be sure that everything works the way you expect it to. You don't test a backup, you test a restore.

Improve this answer
3
  • we have a system in place that fully backs up the SQL DB regularly so I can afford to delete the transaction log backups.
    – Jericho
    Sep 3, 2014 at 16:36
  • If you want to be able to restore to a point in time, or even to a point more recent than your most recent full backup you need to be taking transaction log backups. If you don't need to restore to a point in time and your full backups are sufficient then change your DB to simple recovery.
    – Kenneth Fisher
    Sep 3, 2014 at 21:35
  • If all that you want to do is delete some old files, you could probably code up a job step in Powershell to do that in a couple of minutes.
    – Darin Strait
    Sep 5, 2014 at 12:13
0

Its possibly due to the way you created your SSIS Package.

In order for my log file backups to be removed after a certain date I needed to create 1 maintenance plan with 2 sub plans. Each subplan had a mainentance Cleanup task; One for BAK files and the Other for TRN files.

Because the Maintenance Plan has two sub plans, in order to test you must execute one sub plan at a time.

SQL Server Agent > Jobs > Subplan

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.