AD RMS Log Purging Sample

Applies To: Windows Server 2008, Windows Server 2008 R2

Below is a copy of the logging database purging script used at Microsoft to limit the size of distributed AD RMS logging databases. If you desire to use it in a different environment, it should be reviewed and adapted to the environment’s needs and specific characteristics.

--////////////////////////////////////////////////////////////////////////////////////////////////
-- Description:
--        This script can be used to delete records in the AD RMS Logging source database.
-- ////////////////////////////////////////////////////////////////////////////////////////////////

DECLARE @DeleteEndTime DateTime
DECLARE @MaxErrorInformationId int
DECLARE @MaxCertificateId int
DECLARE @MaxXrmlObjectId int
   
SET @DeleteEndTime = DATEADD(day, -30,  getutcdate())
SELECT @MaxErrorInformationId = MAX(ErrorInformationId) FROM ErrorInformation
SELECT @MaxCertificateId = MAX(CertificateId) FROM Certificate
SELECT @MaxXrmlObjectId = MAX(XrmlObjectId) FROM XrmlObject
-- ////////////////////////////////////////////////////////////////////////////////////////////////
IF OBJECT_ID('tempdb..#Temp_ArchivedSvcRequestIDs_del') IS NOT NULL 
    DROP TABLE #Temp_ArchivedSvcRequestIDs_del
CREATE TABLE #Temp_ArchivedSvcRequestIDs_del
(
    ServiceRequestId int NOT NULL,
    RequestTypeId smallint,
    ServerInformationId smallint,
    ErrorInformationId int,
    RequestUserId int,
    UserAgentId int,
    PRIMARY KEY CLUSTERED (ServiceRequestId ASC)
)
INSERT INTO #Temp_ArchivedSvcRequestIDs_del (ServiceRequestId, RequestTypeId, ServerInformationId, ErrorInformationId, RequestUserId, UserAgentId)
(
    SELECT SRC_SR.ServiceRequestId, SRC_SR.RequestTypeId, SRC_SR.ServerInformationId, SRC_SR.ErrorInformationId, SRC_SR.RequestUserId, SRC_SR.UserAgentId
    FROM  ServiceRequest AS SRC_SR 
    WHERE SRC_SR.CreatedTime <= @DeleteEndTime
)

-- ////////////////////////////////////////////////////////////////////////////////////////////////
-- CLEAN UP
-- ////////////////////////////////////////////////////////////////////////////////////////////////
BEGIN
    DELETE FROM ServiceRequestAndCertificate
    FROM ServiceRequestAndCertificate AS SRC_SVC_CERT
    INNER JOIN #Temp_ArchivedSvcRequestIDs_del
        ON #Temp_ArchivedSvcRequestIDs_del.ServiceRequestId = SRC_SVC_CERT.ServiceRequestId
END

BEGIN
    DELETE FROM ServiceRequest
    FROM ServiceRequest AS SRC_SR
    INNER JOIN #Temp_ArchivedSvcRequestIDs_del
        ON #Temp_ArchivedSvcRequestIDs_del.ServiceRequestId = SRC_SR.ServiceRequestId
END

BEGIN
     --Table ServiceRequest is cleaned up before this.
     DELETE FROM ErrorInformation
     FROM ErrorInformation AS SRC_EI
     WHERE NOT EXISTS (SELECT 1 FROM ServiceRequest AS SRC_SR WHERE SRC_SR.ErrorInformationId = SRC_EI.ErrorInformationId )
 AND ErrorInformationId < @MaxErrorInformationId
END

BEGIN
     --Table ServiceRequestAndCertificate is cleaned up before this.
     DELETE FROM Certificate
     FROM Certificate AS SRC_C
     WHERE NOT EXISTS (SELECT 1 FROM ServiceRequestAndCertificate AS SRC_SVC_CERT WHERE SRC_SVC_CERT.CertificateId = SRC_C.CertificateId) 
 AND CertificateId < @MaxCertificateId
END

BEGIN
     --Table Certificate is cleaned up before this.
     DELETE FROM XrmlObject
     FROM XrmlObject AS SRC_XO
 WHERE NOT EXISTS (SELECT 1 FROM Certificate AS SRC_C WHERE SRC_XO.XrmlObjectId = SRC_C.IssuedPrincipalObjectId) AND
   NOT EXISTS (SELECT 1 FROM Certificate AS SRC_C WHERE SRC_XO.XrmlObjectId = SRC_C.IssuerObjectId) AND
   NOT EXISTS (SELECT 1 FROM Certificate AS SRC_C WHERE SRC_XO.XrmlObjectId = SRC_C.WorkObjectId) AND
   NOT EXISTS (SELECT 1 FROM Certificate AS SRC_C WHERE SRC_XO.XrmlObjectId = SRC_C.FederationPrincipalObjectId)
 AND XrmlObjectId < @MaxXrmlObjectId
END

IF OBJECT_ID('tempdb..#Temp_ArchivedSvcRequestIDs_del') IS NOT NULL 
    DROP TABLE #Temp_ArchivedSvcRequestIDs_del