Tasks | Discuss record retention requirements with the business to establish an appropriate duration. Then create SQL Agent jobs that will pass dates to the stored procedures reflecting the established retention policies. Follow the steps outlined in KB 000033219 to create the SQL Agent jobs. The following SQL will create stored procedures that enforce a retention policy for each of these record types.
/* Company Security Events */
IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_clean_company_security_events]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) BEGIN DROP PROCEDURE [dbo].[usp_clean_company_security_events]; END; GO
/************************************** Copyright (c) 2000-2010 EMC Corporation All Rights Reserved **************************************/
CREATE PROC [dbo].[usp_clean_company_security_events] ( @Date DATETIME , @BatchSize INT = 5000 ) AS BEGIN WHILE EXISTS ( SELECT 1 FROM dbo.tblCompanySecurityEvents WHERE create_date < @Date ) BEGIN PRINT 'Deleting from tblCompanySecurityEvents'; DELETE TOP ( @BatchSize ) FROM dbo.tblCompanySecurityEvents WHERE create_date < @Date; END;
END;
GO
/*Content Hits*/ IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_clean_content_hit]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) BEGIN DROP PROCEDURE [dbo].[usp_clean_content_hit]; END; GO
/************************************** Copyright (c) 2000-2010 EMC Corporation All Rights Reserved **************************************/
CREATE PROC [dbo].[usp_clean_content_hit] ( @Date DATETIME , @BatchSize INT = 5000 ) AS BEGIN WHILE EXISTS ( SELECT 1 FROM dbo.tblContentHit WHERE hit_timestamp < @Date ) BEGIN PRINT 'Deleting from tblContentHit'; DELETE TOP ( @BatchSize ) FROM dbo.tblContentHit WHERE hit_timestamp < @Date; END; END;
GO
/*Data Publication History*/ IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_clean_data_publication_history]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) BEGIN DROP PROCEDURE [dbo].[usp_clean_data_publication_history]; END; GO
/************************************** Copyright (c) 2000-2010 EMC Corporation All Rights Reserved **************************************/
CREATE PROC [dbo].usp_clean_data_publication_history ( @Date DATETIME , @BatchSize INT = 5000 ) AS BEGIN WHILE EXISTS ( SELECT 1 FROM dbo.tblDataPublicationHistoryDetail WHERE create_date < @Date ) BEGIN PRINT 'Deleting from tblDataPublicationHistoryDetail'; DELETE TOP ( @BatchSize ) FROM dbo.tblDataPublicationHistoryDetail WHERE create_date < @Date; END;
WHILE EXISTS ( SELECT 1 FROM dbo.tblDataPublicationHistory WHERE create_date < @Date ) BEGIN PRINT 'Deleting from tblDataPublicationHistory'; DELETE TOP ( @BatchSize ) FROM dbo.tblDataPublicationHistory WHERE create_date < @Date; END;
END;
GO
/*Data Feed History*/ IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_clean_datafeed_history]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) BEGIN DROP PROCEDURE [dbo].[usp_clean_datafeed_history]; END; GO
/************************************** Copyright (c) 2000-2010 EMC Corporation All Rights Reserved **************************************/
CREATE PROC [dbo].[usp_clean_datafeed_history] ( @Date DATETIME , @BatchSize INT = 5000 ) AS BEGIN WHILE EXISTS ( SELECT 1 FROM dbo.tblDatafeedMessage WHERE create_date < @Date ) BEGIN PRINT 'Deleting from tblDatafeedMessage'; DELETE TOP ( @BatchSize ) FROM dbo.tblDatafeedMessage WHERE create_date < @Date; END;
WHILE EXISTS ( SELECT 1 FROM dbo.tblDataFeedHistory WHERE create_date < @Date ) BEGIN PRINT 'Deleting from tblDataFeedHistory'; DELETE TOP ( @BatchSize ) FROM dbo.tblDatafeedHistory WHERE create_date < @Date; END;
END;
GO
/*LDAP Errors*/ IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_clean_ldap_errors]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) BEGIN DROP PROCEDURE [dbo].[usp_clean_ldap_errors]; END; GO
/************************************** Copyright (c) 2000-2010 EMC Corporation All Rights Reserved **************************************/
CREATE PROC [dbo].usp_clean_ldap_errors ( @Date DATETIME , @BatchSize INT = 5000 ) AS BEGIN WHILE EXISTS ( SELECT 1 FROM dbo.tblLDAPErrors WHERE create_date < @Date ) BEGIN PRINT 'Deleting from tblLDAPErrors'; DELETE TOP ( @BatchSize ) FROM dbo.tblLDAPErrors WHERE create_date < @Date; END; END; GO
/*Login History*/ IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_clean_login_history]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) BEGIN DROP PROCEDURE [dbo].[usp_clean_login_history]; END; GO
/************************************** Copyright (c) 2000-2010 EMC Corporation All Rights Reserved **************************************/
CREATE PROC [dbo].usp_clean_login_history ( @Date DATETIME , @BatchSize INT = 5000 ) AS BEGIN WHILE EXISTS ( SELECT 1 FROM dbo.tblLoginHistory WHERE logout_time < @Date ) BEGIN PRINT 'Deleting from tblLoginHistory'; DELETE TOP ( @BatchSize ) FROM dbo.tblLoginHistory WHERE logout_time < @Date; END; END; GO
/*Page Hits*/ IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_clean_page_hit]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) BEGIN DROP PROCEDURE [dbo].[usp_clean_page_hit]; END; GO
/************************************** Copyright (c) 2000-2010 EMC Corporation All Rights Reserved **************************************/
CREATE PROC [dbo].usp_clean_page_hit ( @Date DATETIME , @BatchSize INT = 5000 ) AS BEGIN WHILE EXISTS ( SELECT 1 FROM dbo.tblPageHit WHERE hit_timestamp < @Date ) BEGIN PRINT 'Deleting from tblPageHit'; DELETE TOP ( @BatchSize ) FROM dbo.tblPageHit WHERE hit_timestamp < @Date; END; END; GO
|