SQL 2005 - Backup file deletion problem

[web4fun]

I saw this question posted on an older thread but never saw a reply / solution to the older thread and it has since been closed so I thought I'd ask again.

PROBLEM:

I have a number of databases that I've setup via SQL 2005 DB Maintenance Plan to not only run a FULL backup every Sunday and Wednesday mornings but to also DELETE files older than three days via cleanup task. I've setup the cleanup task to check the D:\..\Backups folder to delete any .BAK files older than three days but it's NOT deleting the older backup files.

I assume the reason is because I have setup the backup to backup the databases in separate folders so the agent does not look at anything other than the specific folder I've defined; i.e. D:\...\Backups, but no sub-folders within the 'Backups' folder...is this why the files are not getting deleted? If so, then how are other folks handling the cleanup of older database files? I really would prefer to to dump all of my backup files in ONE folder. I would have thought that SQL Server 2005 would have included some logic / option to not only check root folders but also sub-folders as well for the cleanup task.

Please advise and Thanks in advance.

 

[SQLBill]

Are you having this problem? On some of our databases we save to subfolders and don't have any problem.

I would say, check your SQL Server error logs, the job, and the Windows Event Viewer logs to see if there are any error messages. Maybe the backup job is actually failing so that it never gets to the delete portion.

-SQLBill

Posting advice: FAQ481-4875

 

[web4fun]

The backup jobs aren't failing as the problem is now I'm getting low on disk space. I read another post where someone was having the same problem but nobody responded with anything which is the reason for the new post. I checked the logs and didn't see anything but I'll check again. Thanks.

 

[SQLBill]

Check the backup folder(s). How many backups are in the folder? What type? And when were they created?

-SQLBill

Posting advice: FAQ481-4875

 

[web4fun]

I have 60 backup subfolders for the user databases and the system databases. Each subfolder contains both .BAK files and .TRN files. The oldest .BAK file has a date modified date of 2/12/07 followed by .BAK files dated 2/13/07, 2/14/07, then the first .TRN file dated 2/15/07 followed by .TRN files dated 2/16/07, 2/17/07, then another .BAK file dated 2/18/07, followed by .TRN files for the last two days. Just f.y.i. when we initially setup the Maintenance Plan(s) we had just set them up to run fulls for the first two days and then I modified the Maint. Plan to run fulls every Wed. and Sun. mornings and in a separate Maint. Plan, I setup a transactional backup to run in between the fulls.

 

[ptheriault]

Are you trying to backup databases and transaction logs from the same maint plan? Are any of the databases set to simple recovery?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[web4fun]

Hello Paul,
Some of the databases are set to simple recovery, while others are set to full recovery.....unfortunately I was not involved in created the databases / initial setup and coming in midway through....you know how that goes..."too many hands in the pot." Does that "bug/problem" about databases being in Simple Recovery mode and not removing the .TRN files still exist in 2005 as it did in SQL 2000?

I have one maintenance plan to run the FULL backups. One maintenace plan to run Transactional backups. One for the SYSTEM backups. Three total maint. plans.

 

[ptheriault]

It sounds like you have them set up properly. usually what happens is if you try to backup the DB and TLOG from the same maint plan it fails when it reaches the DB that is set to simple recovery.

Can you right click on the maintenance plan and select view history. Are there any failures?

The maint plan may be failing before you reach the delete old backups step.



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[web4fun]

Maint. Plan history shows no errors. It looks fine according to the history. The Maintenance Cleanup task is defined to delete anything older than 4 days in the root...'Backups' folder...which is why I'm thinking it might not be traversing down to the subfolders. Or could it be from the fact that after using the Maint. Plan Wizard to create the Maint. Plan, I had to manually add the Cleanup Task to the Plan....which should be fine as during the Wizard setup, there is no Cleanup task option that I see. I have a green arrow going from the backup step to the Cleanup Task step when I view the Plan itself. The very step is the Shrink Database step...so it's very straight forwared...step 1) Shrink the databases if needed, step 2) Backup the databases, and step 3) cleanup any database files older than 4 days...that's it.

 

[ptheriault]

The Maintenance Cleanup task is defined to delete anything older than 4 days in the root...'Backups' folder...

I would change that step to look in the subfolders. Because you have so many maybe you just want to try it for one or two. Also, I find it difficult to manage my backups when they each have their own folder. I find it much simpilar to place them all in the backup folder. But that is just my preference.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[web4fun]

Really? One backups folder eh? I can give it a try and see what happens. If that does work then I'm going to have to change the backup step to back to one folder and then the cleanup task should cleanup those files as I definitely don't want to defined a separate cleanup task to check 60 different folders. I'll give your suggestion a try. Thanks.

 

[web4fun]

Paul, changing the location of the backup folder to a specific subfolder....worked!!!! I ran the T-SQL statement for just the cleanup task and it removed the older files...so looks like I'm going to have to move everything manually to the top level 'Backups' folder and change the existing maintenance plan to just backup there and all should be good....agree?

 

[ptheriault]

I agree, unless you want to make a step to look in 60 different folder!

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[web4fun]

No...not going with the 60 different folder option...thanks for the assistance sir.

 

[ptheriault]

no problem, I'm glad I could help.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[web4fun]

Paul...one last question....should I change the dbs that are currently set to simple recovery to FULL recovery mode?

 

[ptheriault]

what reason would have for doing that? The only reason to change to FULL recovery is if you need to recover that database to a point in time throughout the day. Some questions you need to know the answer to so you can properly set the recovery model...

How transactional is the database? (meaning how frequently does the data change between backups)
How much data can you afford to loose should there be some type of failure? --This will help determine the frequency at which to run the TLOG backup.





- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[web4fun]

Gotcha...thanks Paul.