Solved

SQL Server Maintenance Cleanup Task Intermittently Fails to Remove Old Files

Posted on 2016-10-28
12
62 Views
Last Modified: 2016-11-01
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?
0
Comment
Question by:Jeff Edmunds
  • 6
  • 5
12 Comments
 
LVL 19

Expert Comment

by:Zaheer Iqbal
ID: 41864060
At what point is the cleanup task running ?
Do you have a any other backup jobs running in that time frame?
0
 
LVL 1

Author Comment

by:Jeff Edmunds
ID: 41864087
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
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41866846
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:Jeff Edmunds
ID: 41866848
Only via the maintenance plan.
0
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41866855
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
 
LVL 1

Author Comment

by:Jeff Edmunds
ID: 41866863
Just "bak" with no dot.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41866879
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
 
LVL 1

Author Comment

by:Jeff Edmunds
ID: 41866887
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
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41866896
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
 
LVL 1

Author Comment

by:Jeff Edmunds
ID: 41868476
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
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41868478
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
 
LVL 1

Author Comment

by:Jeff Edmunds
ID: 41868491
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

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question