Windows Server 2012 以降での AD RMS サーバー上の DB メンテナンスについて - その 2

Article
11/07/2017

こんにちは、RMS サポートの益戸です。

以前、「Windows Server 2012 以降での AD RMS サーバー上の DB メンテナンスについて」にてご紹介したクエリについて、今回は改良版をご用意いたしました。

MSIPC のバージョンアップに伴い、以下の公開情報にもあるとおり、今までは多くのデータが保管されることが少なかった UserAgent および、ErrorInformation についても削除を行うよう修正を加えております。ご利用方法については、前回のブログを参照ください。
弊社の複数環境にて動作確認を致しておりますが、ご利用いただく中でご不明な点などございましたら、弊社サポートをご利用いただければ幸いでございます。

[ご参考]

Windows サーバーで AD RMS のイベント ID 84 が発生します。

https://support.microsoft.com/ja-jp/help/4038927/event-id-84-ad-rms-windows-server

SQL 文をコピーする際には、ダブルバイトが存在しないことをご確認ください。

ブラウザによっては、「'」等がダブルバイトになることがあります。ご注意ください。

[Delete 用 SQL 文]

--////////////////////////////////////////////////////////////////////////////////////////////////

-- Description:

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

-- ////////////////////////////////////////////////////////////////////////////////////////////////

----debug print

select 'ServiceRequestAndCertificate',count(*) from ServiceRequestAndCertificate select 'ServiceRequest',count(*) from ServiceRequest select 'Certificate',count(*) from Certificate select 'XrmlObject',count(*) from XrmlObject select 'UserAgent',count(*) from UserAgent --Add ----debug print

DECLARE @DeleteEndTime DateTime

DECLARE @MaxErrorInformationId int

DECLARE @MaxCertificateId int

DECLARE @MaxXrmlObjectId int

SET @DeleteEndTime = DATEADD(day, -1, 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

DELETE FROM UserAgent

where UserAgentId not in (select distinct UserAgentId from ServiceRequest) 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

----debug print

[Truncate 用 SQL 文]

-- //////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- Description:

-- This script can be used to Truncate all the records in following tables: ServiceRequestAndCertificate, ServiceRequest, Certificate and XrmlObject if you want to keep the source logging DB small.

--////////////////////////////////////////////////////////////////////////////////////////////////////////////////

----debug print

ALTER TABLE [dbo].[ServiceRequestAndCertificate] DROP CONSTRAINT [FK_REQUESTID_ServiceRequestAndCertificate_ServiceRequest]

ALTER TABLE [dbo].[ServiceRequestAndCertificate] DROP CONSTRAINT [FK_CERTIFICATEID_ServiceRequestAndCertificate_Certificate]

ALTER TABLE [dbo].[Certificate] DROP CONSTRAINT [FK_ISSUEDPRINCIPALOBJECTID_Certificate_XrmlObject]

ALTER TABLE [dbo].[Certificate] DROP CONSTRAINT [FK_ISSUEROBJECTID_Certificate_XrmlObject]

ALTER TABLE [dbo].[Certificate] DROP CONSTRAINT [FK_WORKOBJECTID_Certificate_XrmlObject]

ALTER TABLE [dbo].[Certificate] DROP CONSTRAINT [FK_FEDERATIONPRINCIPALOBJECTID_Certificate_XrmlObject]

ALTER TABLE [dbo].[ServiceRequest] DROP CONSTRAINT [FK_CLIENTINFOID_ServiceRequest_UserAgent]

ALTER TABLE [dbo].[ServiceRequest] DROP CONSTRAINT [FK_ERRORINFORMATIONID_ServiceRequest_ErrorInformation]

-------------------------------

TRUNCATE TABLE ServiceRequestAndCertificate TRUNCATE TABLE ServiceRequest TRUNCATE TABLE Certificate TRUNCATE TABLE XrmlObject TRUNCATE TABLE UserAgent TRUNCATE TABLE ErrorInformation GO

--------------------------------

ALTER TABLE [dbo].[ServiceRequestAndCertificate] ADD CONSTRAINT [FK_REQUESTID_ServiceRequestAndCertificate_ServiceRequest] FOREIGN KEY ( [ServiceRequestId]

) REFERENCES [ServiceRequest] (

[ServiceRequestId]

)

ALTER TABLE [dbo].[ServiceRequestAndCertificate] ADD CONSTRAINT [FK_CERTIFICATEID_ServiceRequestAndCertificate_Certificate] FOREIGN KEY ( [CertificateId]

) REFERENCES [Certificate] (

[CertificateId]

)

ALTER TABLE [dbo].[Certificate] ADD CONSTRAINT [FK_ISSUEDPRINCIPALOBJECTID_Certificate_XrmlObject] FOREIGN KEY ( [IssuedPrincipalObjectId]

) REFERENCES [XrmlObject] (

[XrmlObjectId]

)

ALTER TABLE [dbo].[Certificate] ADD CONSTRAINT [FK_ISSUEROBJECTID_Certificate_XrmlObject] FOREIGN KEY ( [IssuerObjectId]

) REFERENCES [XrmlObject] (

[XrmlObjectId]

)

ALTER TABLE [dbo].[Certificate] ADD CONSTRAINT [FK_WORKOBJECTID_Certificate_XrmlObject] FOREIGN KEY ( [WorkObjectId]

) REFERENCES [XrmlObject] (

[XrmlObjectId]

)

ALTER TABLE [dbo].[Certificate] ADD CONSTRAINT [FK_FEDERATIONPRINCIPALOBJECTID_Certificate_XrmlObject] FOREIGN KEY ( [FederationPrincipalObjectId]

) REFERENCES [XrmlObject] (

[XrmlObjectId]

)

ALTER TABLE [dbo].[ServiceRequest] WITH CHECK ADD CONSTRAINT [FK_CLIENTINFOID_ServiceRequest_UserAgent] FOREIGN KEY([UserAgentId]) REFERENCES [dbo].[UserAgent] ([UserAgentId]) GO

ALTER TABLE [dbo].[ServiceRequest] CHECK CONSTRAINT [FK_CLIENTINFOID_ServiceRequest_UserAgent]

ALTER TABLE [dbo].[ServiceRequest] WITH CHECK ADD CONSTRAINT [FK_ERRORINFORMATIONID_ServiceRequest_ErrorInformation] FOREIGN KEY([ErrorInformationId]) REFERENCES [dbo].[ErrorInformation] ([ErrorInformationId]) GO

ALTER TABLE [dbo].[ServiceRequest] CHECK CONSTRAINT [FK_ERRORINFORMATIONID_ServiceRequest_ErrorInformation]

----debug print

Windows Server 2012 以降での AD RMS サーバー上の DB メンテナンスについて - その 2

Additional resources