SQL Server Maintenance Cleanup Task Intermittently Fails to Remove Old Files

I've created a Maintenance Plan on SQL 2008 R2 Standard Edition with three tasks - one backs up all user databases to disk, one is a maintenance cleanup task which deletes "bak" files older than 2 days, and one is a simple Notify Operator task which should email me if either of the first two tasks fails. There are separate backup destination directories - one for each database on the SQL instance. The backup type is set to "Full" and the server connection is local. The recovery model on these databases is "Simple" as there is no need for point-in-time recovery for the applications being supported. The plan has been running for the better part of a year now and has not been recently changed.

Most days, for most databases, the plan runs successfully and the cleanup task leaves only two backup files in the destination directory (the most current and the day before). On occasion, I'll find one or two databases whose backup directories contain 3 files... It doesn't fail, it just doesn't remove the oldest file. I had assumed this was simply a timing issue. Last night the backup job failed due to low available drive space. When I checked the backup folders, every backup directory contained at least three files and in some cases four files. What could be causing this?
LVL 2
Jeff EdmundsApplication Developer/SQL DBAAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It might be a bug then. To be honest I never use the Maintenance Plan from SQL Server but this one from Ola Hallengren. It work much better and it's accepted by a large number of DBAs around the world.

Btw, in you clean up task do you have "bak" or ".bak" as the file extension? The dot might do a difference here.
0
 
Zaheer IqbalTechnical Assurance & ImplementationCommented:
At what point is the cleanup task running ?
Do you have a any other backup jobs running in that time frame?
0
 
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
The cleanup task runs after the backup task completes. There are no other backup tasks on that instance running at the same time. There is a separate SQL instance on the server with a similar maintenance plan setup and the schedule is close, however the backup is saving to a different location. I thought it might be because of the slight overlap in the timing, however I've seen similar effects (with the occasional 3 files being left instead of two) on some of our other servers where the timing of the maintenance plans for the separate instances doesn't overlap at all. This is the first time I've seen as many as four files left behind for every database on the instance and had the backup fail due to drive space limitations.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
On occasion, I'll find one or two databases whose backup directories contain 3 files... It doesn't fail, it just doesn't remove the oldest file.
Were those backups ran manually instead from the backup maintenance task?
0
 
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
Only via the maintenance plan.
0
 
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
Just "bak" with no dot.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just "bak" is ok. Just wanted to confirm that because many people typed ".bak" and that doesn't work.
I still thinking that's maybe a bug so my suggestion is to recreate the cleanup task.
0
 
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
I'll give that a shot. I'm not in the office today, but will post back results when I've had a chance to try your suggestion. I will also take a look at the third party tool you recommended. Thanks!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I will also take a look at the third party tool you recommended.
The tool is a T-SQL script that you need to run in the SQL Server instance and it will produce SQL jobs so the only thing you really need to do besides running the script is to schedule the created jobs.
0
 
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
Hi Vitor.

The maintenance plan has run normally now since the day I first posted this. All of the backup folders are back to their normal 2 files per day (current and previous day). I didn't change anything or do anything other than to remove the older backups which were "stuck" in there. However, I'm marking your answer as the best solution. Even though things seem to have gotten back to normal on their own, I think I'm going to rebuild the maintenance plan as you suggested just to see if that prevents it going forward. Also, the script you suggested looks intriguing and I'm going to take a look at it.

Thanks for your help!

Jeff
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
About Ola's solution it has more than backup tasks. It also provides reindex tasks that you can and should schedule it to run in a day basis during low activity period (usually during night) so it will keep the index fragmentation in a very low level improving the database performance.
Cheers
0
 
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
I saw that - looks like it handles integrity checks too, which is very cool. Right now I'm doing Integrity checks with a separate maintenance plan. I have a SQL Agent Job set up for most of our servers which runs a script daily that checks index fragmentation levels and then emails me each morning if there are any which need to be reorganized or rebuilt.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.