Cleanup of recycle bin

Roel de Bruijn asked on February 9, 2023 15:22

We have a customer who has close to 400K media files in the recyclebin.

I wrote a simple stored procedure which deletes all after X days old, in batches of N records, the following delete statement in a loop:

delete top (50) from CMS_ObjectVersionHistory 
where VersionObjectType = 'media.file'
and VersionDeletedWhen < @ArchiveDate

My question:
I assume this is all I need to do, but just to be sure, am I forgetting something?

Using K12SP1 and K13

Correct Answer

Brenden Kehren answered on February 9, 2023 15:49

I'd highly suggest turning off versioning for Media Files in Settings > Versioning & Synchronization > Use object versioning for > Media files. Then review the setting key Settings > Versioning & Synchronization > General Delete objects to recycle bin and possibly set that to Versioned objects only.

Your stored proc is good, however, it may take a bit to delete them so maybe clear out some of the fields first for those media file objects then run a full delete on them.

0 votesVote for this answer Unmark Correct answer

Recent Answers

Roel de Bruijn answered on February 9, 2023 16:13

Hi Brenden,

I agree, better not to move deleted media to recyclebin, but it's up to the customer to decide. I think we'd better add a scheduled task to cleanup the recyclebin, and another big one: the email (attachments).

I don't know what you mean by 'clear out some of the fields'.

The complete stored proc looks like:

CREATE PROCEDURE [dbo].[sp_MediaHistoryCleanup]
  @ArchiveDays int = 30
AS
BEGIN
SET NOCOUNT ON;

-- example use:
-- exec sp_MediaHistoryCleanup 30

declare @deleted_rows int
declare @breakbyerror int = 0
declare @ArchiveDate datetime = DATEADD(d,-@ArchiveDays, GetDate())

-- backup log only supported with backupplan full
-- declare @databaseName varchar(1000) = DB_NAME()

print convert(varchar(25), getdate(), 121) + ': Start'
RAISERROR(N'', 0, 1) WITH NOWAIT

set @deleted_rows = 1;
while (@deleted_rows > 0 and @breakbyerror = 0)
begin
  begin try
    delete top (50) from CMS_ObjectVersionHistory 
    where VersionObjectType = 'media.file'
    and VersionDeletedWhen < @ArchiveDate
    set @deleted_rows = @@rowcount;

    print convert(varchar(25), getdate(), 121) + ':   ' + cast(@deleted_rows as varchar(max)) + ' records deleted'
    RAISERROR(N'', 0, 1) WITH NOWAIT
  end try
  begin catch
    select  ERROR_NUMBER() AS ErrorNumber
           ,ERROR_SEVERITY() AS ErrorSeverity
           ,ERROR_STATE() AS ErrorState
           ,ERROR_PROCEDURE() AS ErrorProcedure
           ,ERROR_LINE() AS ErrorLine
           ,ERROR_MESSAGE() AS ErrorMessage;

    print convert(varchar(25), getdate(), 121) + ': Error occurred'
    RAISERROR(N'', 0, 1) WITH NOWAIT

    set @breakbyerror = 1
  end catch
  -- backup log @databaseName to  disk = N'NUL:'

end
print convert(varchar(25), getdate(), 121) + ': Done'

END

And yes, it takes ages to delete :(

0 votesVote for this answer Mark as a Correct answer

Not Applicable answered on February 9, 2023 16:32

Did something similar to clean up all objects and pages > x days in the recycle bin, with a scheduled task. If you go the SQL way, you should also set the ObjectCheckedOutVersionHistoryID to NULL in CMS_ObjectSettings for these objects I believe.

0 votesVote for this answer Mark as a Correct answer