Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Maintenance Plan not deleting physical backup file Expand / Collapse
Author
Message
Posted Monday, March 2, 2009 12:59 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 2:26 PM
Points: 386, Visits: 626
I've got a strange error. We have a network share we back up our servers to. About a month ago we got an error because the drive was full. Taking a look at it, none of the backup files had been deleted since we upgraded to 2005.

After manually deleting enough files to get everything running smoothly i took a look at the maintenance plan and before the back up operation begins it is supposed to delete all jobs and backup files older than 4 days.

So I continued to watch as the job ran nightly, but no physical files have been deleted.

So I manually ran on one of the servers,

declare @oldestDate datetime

set @oldestDate = cast(dateadd(dd, -4,CURRENT_TIMESTAMP) as datetime)

exec msdb.dbo.sp_delete_backuphistory @oldest_date=@oldestDate

it deleted the backup history but of course not the physical files, i was debating writing a script that would delete them but figured i'd ask for help before I do, as I would prefer not to have to do this manually.


Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Post #666839
Posted Monday, March 2, 2009 1:49 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:26 AM
Points: 4,358, Visits: 9,536
In 2005 - you have to add the Maintenance Cleanup Task to the maintenance plan and configure it. Sounds to me like that does not exist in the plan and that is why you are not seeing the files cleaned up.

You will need two tasks - one for backup files and one for transaction log backup files.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #666886
Posted Monday, March 2, 2009 3:03 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 29, 2014 9:29 AM
Points: 214, Visits: 647
Also check to see what you are trying to delete - if you put .BAK in the window, it won't delete. Needs 'BAK' only, assuming that your backup is using the standard .bak extension.




Lee Everest

Post #666934
Posted Monday, March 2, 2009 5:09 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 17, 2009 5:33 PM
Points: 81, Visits: 299
Manually use this query and schedule it..

declare @dt datetime

select @dt=getdate() - N-- N is the files with Ndays old
EXECUTE master.dbo.xp_delete_file 0,N'Backup Location',N'BAK',@dt
Post #666976
Posted Saturday, March 7, 2009 12:42 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 2:26 PM
Points: 386, Visits: 626
Hey Guys,
Thanks for the reply's. I didn't have to try the script the first 2 options worked for me. It was funny the first time it didn't and then i realized that I had put the period in the .bak file extension just like you said.
I had been waiting until the weekend to get it resolved and knocked it out quickly in several different environments!

Thanks to all!


Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Post #670968
Posted Tuesday, May 11, 2010 1:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 1:10 PM
Points: 19, Visits: 201
Hi, I'm having the same issue, I've got the cleanup job scheduled on the Maintenance Plan, it shows as it runs fine, finishes successfully but it doesn't delete the backups and the transaction logs. This was working fine but it started to work this way a couple days back.

I tried this solution, I run the manual script:
declare @dt datetime

select @dt=getdate() - 1-- N is the files with Ndays old
EXECUTE master.dbo.xp_delete_file 0,N'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\',N'BAK',@dt

It succeeds and it still doesn't remove the bak files.

Any idea?

I'm running:
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Thanks
Lucas

Post #919990
Posted Tuesday, May 11, 2010 2:10 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
Try N'.BAK' instead of N'BAK'.
Post #920009
Posted Tuesday, May 11, 2010 2:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 1:10 PM
Points: 19, Visits: 201
MANU-J. (5/11/2010)
Try N'.BAK' instead of N'BAK'.


Same thing, completes successfully, but it does nothing.
Post #920013
Posted Tuesday, May 11, 2010 2:27 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
Try N'*.*' instead of 'BAK' if there are no other files than backup files in that folder.
Post #920024
Posted Tuesday, May 11, 2010 2:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 1:10 PM
Points: 19, Visits: 201
MANU-J. (5/11/2010)
Try N'*.*' instead of 'BAK' if there are no other files than backup files in that folder.


Same thing, completes successfully, and then it does nothing, the bak's are still there :s
Post #920027
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse