December 13, 2010 at 4:57 am
Hi,
I need to create a job that cleans backups and log backups that are older than a month.
I do not want to create a maintenance plan, what i would like to create is a single job.
Is it possible?
How to do something like this?
Tks,
Pedro
December 13, 2010 at 6:18 am
The Cleanup task in Maintenance Plans is the easiest way of doing this. Since you do not want to create Maintenance Plans, you can make use of a custom as explained here
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
December 13, 2010 at 12:18 pm
You can use xp_delete_file (http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx) or you could write something using xp_cmdshell (assuming you have xp_cmdshell enabled).
December 13, 2010 at 1:31 pm
I use the following stored proc to delete old backup files as a step within a standard scheduled job.
usage: usp_DeleteOldBackupFiles <path>, <file extention>, <age_hours>
i.e. usp_DeleteOldBackupFiles 'D:\MSSQL_DBBackups', 'bak', 36
usp_DeleteOldBackupFiles 'D:\MSSQL_DBBackupsLogs', 'trn', 72
--====================================================================
CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles]
@path nvarchar(256),
@extention nvarchar(10),
@age_hrs int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DeleteDate nvarchar(50)
DECLARE @DeleteDateTime datetime
SET @DeleteDateTime = DateAdd(hh, -@age_hrs, GetDate())
SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))
EXECUTE master.dbo.xp_delete_file 0, @path, @extention, @DeleteDate, 1
END
--============================
-- xp_delete_file information
--============================
-- xp_delete_file actually checks the file header to see what type of file it is and will only delete certain types such
-- as database and log backups. I suppose they expanded this to certain types of log files as well but as you say this is
-- not documented by MS. Just be aware that it will not delete just any file type
-- First argument is:
-- 0 - specifies a backup file
-- 1 - specifies a report file
-- (I'm not sure what the difference between a "backup file" and a "report file" is, since you specify the extension of files
-- you're deleting with the third argument.)
--
-- Fifth argument is whether to delete recursively.
-- 0 - don't delete recursively (default)
-- 1 - delete files in sub directories
--====================================================================
I tweeked this from information I found on the net - Unfortunately, I can't remember where from so can't offer credit where it's due 🙁
Cheers
December 14, 2010 at 6:53 am
Thank you for your reply.
I have two question about your stored procedure:
1) do i have to activate in the Surface area configuration the xp_cmdshell to use your procedure?
2) Where do you stored this stored procedure? In the MSDB or in the Master Database?
Thank you
December 14, 2010 at 8:10 am
I am thinking in using your stored procedure:
use msdb
go
CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles]
@path nvarchar(256),
@extention nvarchar(10),
@age_hrs int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DeleteDate nvarchar(50)
DECLARE @DeleteDateTime datetime
SET @DeleteDateTime = DateAdd(hh, -@age_hrs, GetDate())
SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))
EXECUTE master.dbo.xp_delete_file 0, @path, @extention, @DeleteDate, 1
END
/* I want to create this procedure in the MSDB database.*/
Then, in each job (i have two jobs, one to do database backups and another to do log backups) i add a new step, it will be the first step of each job. The code is this:
For database backups:
usp_DeleteOldBackupFiles 'D:\MSSQL_DBBackups', 'bak', 720
For log backups:
usp_DeleteOldBackupFiles 'D:\MSSQL_DBBackupsLogs', 'trn', 720
/* 720 hours = aprox. 1 month, 30 days*/
As i told, this will be the first step of each job.
When i try to save this first step, i receive the following message from SQL Server:
Warning: The following job steps cannot be reached with the current job step flow logic:
[1] DeleteBackups
Is this the intended behavior?
Can you help with this? why SQL Server sends a message like this?
December 14, 2010 at 10:49 am
This happens if your job has multiple steps say Step1 and Step2.
For each step, you need to specify the SQL Server agent what to do in case of success or failure of that step.
In this case , suppose that
Step 1 : Delete Old Backups
Step 2 : Take Backup
Then for Step1 and Step2 you need to specify some thing like this
Step1 : On Success ---> Goto Step 2
On Failure ---> Quit the job reporting failure or Goto Step 2
Step2 : On Success ---> Quit the job reporting success.
On Failure ---> Quit the job reporting failure.
Thank You,
Best Regards,
SQLBuddy
December 14, 2010 at 12:37 pm
Hi OldHand
1) do i have to activate in the Surface area configuration the xp_cmdshell to use your procedure?
No. Thats not required.
2) Where do you stored this stored procedure? In the MSDB or in the Master Database?
I always create a DBA/Management database on my SQL servers to hold these type of stored procs/functions/tables/logging/performance info/service broker queues...etc. Personally, I try to avoid adding customisations to any of the system databases, although I'm aware this is commonly done.
Cheers
December 14, 2010 at 2:19 pm
I have already told the job that (on sucess go to and on failure go to...)
Still same problem.
Any ideias?
December 14, 2010 at 2:56 pm
Hi river1
The issue you are seeing is definately related to the flow control you have set up in the job.
I suggest you open the job, and check the steps very carefully. Look at the order that the steps are listed in the job. They may not be listed in the order you create them....i.e. If you create a step (Delete Old Backups), then simply click 'new' to create a second job (Do Backups), the order will be: Step 1. Do Backups, Step 2.Delete Old Backups.
You can change the order with the 'move step' buttons,
If you still have problems, script the job and post it here
Cheers
February 18, 2011 at 11:41 am
You can use this script. work well with 2005 and 2008 versions and I am using this for last 4 years and never had issue.
-- Delete backup files from any folder. just change path. Add this TSQL code as step2 to scheduled plan
DECLARE @currentdate datetime
DECLARE @olddate datetime
set @currentdate = CURRENT_TIMESTAMP
set @olddate = @currentdate - 2 -- Here change the number as required for how many days backup you want to keep.
EXECUTE master.dbo.xp_delete_file 0,N'D:\Backup directory path here',N'bak',@olddate,1
SQL DBA.
August 5, 2012 at 2:24 am
When i try to save this first step, i receive the following message from SQL Server:
Warning: The following job steps cannot be reached with the current job step flow logic:
[1] DeleteBackups
Is this the intended behavior?
Can you help with this? why SQL Server sends a message like this?
Hi,
Check this blog for the cause and resolution of this error.
Prashant
Check out my blog at http://sqlactions.com
July 24, 2016 at 5:03 pm
Hi Guys,
You can use SQL Maintenance task (Management > Maintenance Plans > New > Cleanup Task) to delete any files based on the age of it.
If you want to delete files by calling a stored procedure, you can use the system stored procedure:
EXECUTE master.dbo.xp_delete_file 0,N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup',N'bak',N'2016-07-17T23:02:07'
April 20, 2017 at 5:02 am
dwayne.bace - Tuesday, December 14, 2010 2:56 PMHi river1The issue you are seeing is definately related to the flow control you have set up in the job.I suggest you open the job, and check the steps very carefully. Look at the order that the steps are listed in the job. They may not be listed in the order you create them....i.e. If you create a step (Delete Old Backups), then simply click 'new' to create a second job (Do Backups), the order will be: Step 1. Do Backups, Step 2.Delete Old Backups.You can change the order with the 'move step' buttons, If you still have problems, script the job and post it hereCheers
Hi how to add check condition
unless db backup success then delete old backup if db backup failure of any reason then don't delete old backup
April 22, 2017 at 4:36 pm
You can try using the power shell script... we are using the same.
https://gallery.technet.microsoft.com/scriptcenter/Delete-files-older-than-x-13b29c09
@JayMunnangi
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply