Question about Maintenance plans and remove files older than X days

Question

Question about Maintenance plans and remove files older than X days

TO-295120

SSC Enthusiast

Points: 151
More actions
January 19, 2006 at 6:31 am

#188921

Hi,
I have installed couple of SQL Server 2000 environments and maintenance plans that makes a backup of database and transaction log every night. I have used an option remove files older than 2 days, but this doesn't work with TRN -files (transaction logs). All BAK.files (database backups) will be removed from backup folder nicely but no TRN files will be removed.
Have You had similar problems and found out some solution?
Thank You..

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

allen davidson SSCertifiable Points: 7042 More actions · Answer 1

I've not had this problem but I think you specify the retention period for the Complete Backup and Transaction Log Backup seperately. Have you set both?

TO-295120 SSC Enthusiast Points: 151 More actions · Answer 2

Hi,

I have set the maintenance plan the way that both backups will be made in same maintenance plan. I haven't tried this separately. Did I answer to Your question

Joe Nakanishi-3 SSC Eights! Points: 828 More actions · Answer 3

I think Allen was referring to the fact that you set up retention periods for transaction files and backup files seperately within the maintenance plan.

Did you check the job history?

TO-295120 SSC Enthusiast Points: 151 More actions · Answer 4

Hi again,

I'm sorry I don't exactly understand what You mean with this term "retention period". The schedule in my maintenance plan is like this:

- Database: Every day at 2.00 AM

- Transaction log: Ever day at 2.00 AM

In other words at same time, should I change this for example the way that trn would be made at 2.30 AM ?

I have checked the job history and there is nothing that would refer to remove TRN files. That's weird..

allen davidson SSCertifiable Points: 7042 More actions · Answer 5

On the Complete Backup tab there is the option 'Remove files older than ..'. this relates to retention of the Complete Backup.

This same option also appeaers on the Transaction Log Backup and relates to retention of Transaction Log backups (the .TRN files).

Regarding timing: if this is an OLTP database then I would be tempted to backup the Transaction log more often say every hour or even less depending on how much exposure to data loss you are willing to take.

TO-295120 SSC Enthusiast Points: 151 More actions · Answer 6

Ok, now I understand what You mean.

Yes, I have set both schedules. One "remove files..." to BAK-files and another for TRN-files.

Joe Nakanishi-3 SSC Eights! Points: 828 More actions · Answer 7

Check the transaction log backup job history.

How about anti-virus software?

Joe Nakanishi-3 SSC Eights! Points: 828 More actions · Answer 8

Is your DB in Simple recovery? If so, no transaction log backups aren't allowed.

TO-295120 SSC Enthusiast Points: 151 More actions · Answer 9

DB is in Full recovery mode.

What should I check from job backup history?

And what about anti virus software?

I heard from aome "expert" that it may work if I don't save the BAK and TRN files to own folder? Do You know what is the point of this statement?

Joe Nakanishi-3 SSC Eights! Points: 828 More actions · Answer 10

Check the job history for failures or notifications. They might provide some info.

Anti virus software may be scanning the file at the same time SQL is trying to delete the file. It's a long shot, but...

I keep BAKs and TRNs in the same directory and don't have any issues with the maintenance plan deleting the old files.

Jpotucek SSCoach Points: 18583 More actions · Answer 11

If you are using one maintenance plan for both Full and xaction log backups, did you choose User Databases only or are you trying to backup ALL databases including the Master, model and msdb with the same maintenance plan?

If you have one maintenance plan for ALL databases (User and System Databases) then you should do the transaction log backups under a seperate maintenance plan. you CANNOT do transaction log backups on the System databases - that might be the problem

terazu SSC Enthusiast Points: 145 More actions · Answer 12

I use Backup Database Task to backup specific user databases. And that works fine.

It is just that the Maintenance Cleanup Task after the backup doesn't work. It says that it runs successfully but the .bak files older than 4 weeks are still sitting in the folder.

I have checked the security and file permission. And they look fine. My other maintenance plan seems to work.

I noticed when I create maintenance plan using wizard, it links Backup Database Task to Maintenance Cleanup Task on Completion not Success. What's the difference? But I can confirm all my database backup were run successfully.