Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have a maintenance plan to do a full backup every night at midnight and a transaction backup every hour.

Both have "Delete backups older than 3 days" checked in the wizard

Here's the SQL from the Job:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 8DBCD331-76C8-4736-87ED-FB840696AE63 -WriteHistory  -VrfyBackup -BkUpMedia DISK -BkUpLog "T:\SQL Backups" -DelBkUps 3DAYS -CrBkSubDir -BkExt "TRN"'

The full backups seem to be cleaning up correctly. However, I'm seeing Transaction logs going back for weeks and we're having to manually go in and clear space because of this.

Is there any reason why this would happen? Permissions? Job taking to long?

share|improve this question
1  
Anything in the log files of the server? –  Peter Schuetze Jan 13 '10 at 15:29
add comment

2 Answers

up vote 2 down vote accepted

If I remember correctly, that was kind of buggy in SQL Server 2000. Personally I don't set this option in my maintenance plans, I schedule a batch file to delete the backup files after they've been backed up to tape. Maybe these will help:

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

http://www.componentworkshop.com/blog/2009/07/01/sql-server-2005-maintenance-cleanup-task-does-not-delete-backup-files

http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic24757.aspx

share|improve this answer
add comment

When I usually see this it's due to a job in a maintenance plan failing. Essentially the maintenance plan backs up a database before it deletes old backups. When it does delete the old backups it does it strictly on a time basis. Meaning that when the time comes it will delete a full backup but leave the now useless tranlog backups for that period between the deleted backup and the next full backup.

I wrote this script a few years back that allows you to delete sets of backups and you can schedule it to run before the maintenance plan backup.

http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30905/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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