I am thinking in using this stored procedure:
CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles]
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
/* 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:
Is this the intended behavior?
Can you master help with this? why SQL Server sends a message like this?