Solved

SQL Server Maintenance Cleanup Task Intermittently Fails to Remove Old Files

Posted on 2016-10-28
12
34 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
 

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 45

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
 

Author Comment

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

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
 

Author Comment

by:Jeff Edmunds
ID: 41866863
Just "bak" with no dot.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 45

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
 

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 45

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
 

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 45

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
 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to calculate iops? 12 27
sql query to reportserver  table error 3 23
SQL Maintenance Plan 3 17
Caste datetime 2 24
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now