Because SimplyReports can hang when trying to load a long list of record sets our consortium deletes them on a regular basis.
Here is our criteria:
- The record set hasn’t been modified in the past 18 months
- Performing a bulk change on records within the record set does NOT update the record set’s modification date.
- The record set NAME OR NOTE field does NOT contain the word KEEP (case-insensitive)
- If either field contains the word KEEP then the record set will not be deleted regardless of age.
We have a SQL job that does the cleanup work, if others are interested, let us know and we can post it to the forum.
What about you? Do you delete record sets on a regular basis? What is your criteria?
I would definitely be interested in the SQL job! Many of our staff create record sets that are meant to be temporary but then end up being on the system for years. I’d love to start getting those cleaned up – thanks for sharing!
Here’s the code for our record set cleanup job:
declare @rsids table ( rsid int )
insert into @rsids
from Polaris.Polaris.RecordSets rs
where (coalesce(rs.ModificationDate, rs.CreationDate) < dateadd(month, -18, getdate()) and rs.name + coalesce(rs.Note, '') not like '%keep%' )
or (rs.CreatorID = 1 and rs.Name like 'Ebook Import%')
begin tran deleters
delete from Polaris.Polaris.BibRecordSets where RecordSetID in ( select * from @rsids )
delete from Polaris.Polaris.ItemRecordSets where RecordSetID in ( select * from @rsids )
delete from Polaris.Polaris.PatronRecordSets where RecordSetID in ( select * from @rsids )
delete from Polaris.Polaris.AuthorityRecordSets where RecordSetID in ( select * from @rsids )
delete from Polaris.Polaris.RecordSets where RecordSetID in ( select * from @rsids )
commit tran deleters
if (@@trancount > 0)
rollback tran deleters
select ERROR_NUMBER() [ErrorNumber]