Solved

SQL Server Maintenance Cleanup Task Intermittently Fails to Remove Old Files

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

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

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

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 47

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 47

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

786 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