Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Maintenance Plan not deleting physical backup file


Maintenance Plan not deleting physical backup file

Author
Message
SQLBalls
SQLBalls
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 630
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
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4461 Visits: 9829
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

ab5sr
ab5sr
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 699
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


Sudiendra
Sudiendra
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 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
SQLBalls
SQLBalls
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 630
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
Luk - ARG
Luk - ARG
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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
MANU-J.
MANU-J.
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1712 Visits: 8766
Try N'.BAK' instead of N'BAK'.
Luk - ARG
Luk - ARG
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 201
MANU-J. (5/11/2010)
Try N'.BAK' instead of N'BAK'.


Same thing, completes successfully, but it does nothing.
MANU-J.
MANU-J.
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1712 Visits: 8766
Try N'*.*' instead of 'BAK' if there are no other files than backup files in that folder.
Luk - ARG
Luk - ARG
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search