Any way to count the number of files deleted from share path?

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  April 23, 2015 at 1:44 pm

  #307332

  SET NOCOUNT ON

  Declare @daysOld int,@deletedate nvarchar(19) ,@strDir varchar(250)

  declare @cmd11 nvarchar(2000)

  declare @mainBackupDir varchar(2000),

  @result1 nvarchar(max);

  -- Check and Delete Temp table.

  IF OBJECT_ID('TempDB..#Dir_list','U') IS NOT NULL

  DROP TABLE #Dir_list

  set @mainBackupDir = @chkdirectory -- location from where I need to delete.

  create table #Dir_list (strDir nvarchar(max) )

  set @cmd11 = 'dir ' + @mainBackupDir + ' /b /OD'

  --Print @cmd11

  insert into #Dir_list

  exec xp_cmdshell @cmd11

  set @daysold = 3

  SELECT

  @deletedate = CONVERT(VARCHAR(19), DATEADD(hh, -48, GETDATE()), 126)

  -- Close all the opened Cursors.

  IF (SELECT Cursor_status('global', 'DeleteBackup')) >= -1

  BEGIN

  IF (SELECT Cursor_status('global', 'DeleteBackup')) > -1

  BEGIN

  CLOSE DeleteBackup

  END

  DEALLOCATE DeleteBackup

  END

  DECLARE DeleteBackup CURSOR FOR

  select strDir from #Dir_list

  where strDir is not null

  OPEN DeleteBackup

  FETCH NEXT FROM DeleteBackup

  INTO @strDir

  WHILE @@FETCH_STATUS = 0

  BEGIN

  set @cmd11 = @mainBackupDir + '\' + @strDir

  --print 'Deleting files from ' + @cmd11 + ' , that are older than ' + @deletedate

  /* Full backup */

  --print @cmd11

  set @cmd11 = 'EXECUTE master.dbo.xp_delete_file 0,N''' + @cmd11 +''' ,N''bak'',N''' + @deletedate + ''''

  --exec sp_executesql @cmd11

  EXEC sp_executesql @cmd11

  --IF (@result1 = 0)

  -- PRINT 'Deletion Success'

  --ELSE

  -- PRINT 'Deletion Fail';

  --END

  --EXEC sp_executesql @result1 = @cmd11

  --SET @result1 = @@rowcount;

  --PRINT @result1

  /* Transaction log */

  --set @cmd11 = @mainBackupDir + '\' + @strDir

  --set @cmd11 = 'EXECUTE master.dbo.xp_delete_file 0,N''' + @cmd11 +''' ,N''trn'',N''' + @deletedate + ''''

  --print @cmd11

  --exec sp_executesql @cmd11

  FETCH NEXT FROM DeleteBackup

  INTO @strDir

  --PRINT 'Number of BAckup Files deleted from UNC: ' + convert(varchar(10), @result1)

  END

  CLOSE DeleteBackup

  DEALLOCATE DeleteBackup

  Thanks.

• Jeff Moden

  SSC Guru

  Points: 1004641

  More actions

  April 23, 2015 at 4:56 pm

  #1793148

  What's wrong with just adding a counter to the loop?

  Ah... never mind. Didn't look to see how you were deleting the files.

  You could use xp_Dirtree before and after each delete command and just take a difference in counted rows. That difference will be the row deleted unless someone inserts a new file in the meantime.

  Personally, I use either xp_CmdShel or sp_OA* to get a complete list of the files I want to delete and store them in a temp table instead of using a cursor. That way I have all the details and can do the same as the undocumented stored procedure that you used plus get counts, etc.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)