Solved

SQL Server Maintenance Cleanup Task Intermittently Fails to Remove Old Files

Posted on 2016-10-28
12
76 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

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

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 50

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 50

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 50

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

688 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