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