Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics CRM forum
Unanswered

Add the AuditBase table back to the AuditPScheme Partition

Posted on by 35

I few years ago coworker of mine created an AuditBase table purge process that he ran a few times to reduce the size of the AuditBase table in CRM. After digging through his scripts I noticed that he took the approach to create a temp table and copy only the records he wanted to keep into it, then he dropped the AuditBase table and renamed his temp table to AuditBase. That is fine and it works without any issues. However, when doing this he did not add it back to the partition and he did not add back the 5 indexes on the Audit table. So I'm working through the process to get this set back up correctly. I was able to get it fixed in one of our development environments by doing the same sort of thing that he did, create a temp table, copy the audit records into, drop the audit table, then rename the temp table to AuditBase. But this time I added the partition and indexes back. It works fine. However, I'm trying to figure out how to do this without having to copy the audit records into a temp table. I should be able to just add the AuditBase table back to the partition and add back the indexes. But I am not sure how to add the partition back. I hoping someone can point me in the right direction. 

This is what I came up with using a temp table. Can someone tell me how to just add the partition back to the AuditBase table without having to use a temp table? The reason I want to do that is because my Audit table in production has over 1 billion audit records and is about 2TB in size. So I don't want to copy something like that in production if I don't have to. 

USE [CRM_MSCRM]
GO

-- 2.1 Create Audit table on AuditScheme

CREATE TABLE [dbo].[AuditBase-TEMP](
	[AttributeMask] [nvarchar](max) NULL,
	[TransactionId] [uniqueidentifier] NOT NULL,
	[Action] [int] NULL,
	[ObjectId] [uniqueidentifier] NOT NULL,
	[ObjectIdName] [nvarchar](1) NULL,
	[UserId] [uniqueidentifier] NOT NULL,
	[ChangeData] [nvarchar](max) NULL,
	[CreatedOn] [datetime] NOT NULL,
	[Operation] [int] NOT NULL,
	[AuditId] [uniqueidentifier] DEFAULT (newsequentialid()) NOT NULL,
	[CallingUserId] [uniqueidentifier] NULL,
	[ObjectTypeCode] [int] NULL,
	[RegardingObjectId] [uniqueidentifier] NULL,
	[RegardingObjectIdName] [nvarchar](4000) NULL,
	[UserAdditionalInfo] [nvarchar](400) NULL,
) ON AuditPScheme (CreatedOn)

-- 2.2 Copy audit records from AuditBase to AuditBase-TEMP

INSERT INTO [dbo].[AuditBase-TEMP] 
(
    [AttributeMask], 
    [TransactionId], 
    [Action], 
    [ObjectId], 
    [ObjectIdName],
    [UserId], 
    [ChangeData], 
    [CreatedOn], 
    [Operation], 
	[AuditId],
    [CallingUserId], 
    [ObjectTypeCode]
)
SELECT 
    [AttributeMask], 
    [TransactionId], 
    [Action], 
    [ObjectId], 
    [ObjectIdName], 
    [UserId], 
    [ChangeData],
    [CreatedOn], 
    [Operation],
	[AuditId],
    [CallingUserId], 
    [ObjectTypeCode] 
FROM [dbo].[AuditBase]

---- 2.3 Drop AuditBase table

IF EXISTS (SELECT * FROM sys.tables WHERE Name = 'AuditBase')
BEGIN
	print 'Dropping Audit table AuditBase'
	DROP Table AuditBase
END

---- 2.4 Rename AuditBase-TEMP table to AuditBase

EXEC sp_rename 'AuditBase-TEMP', 'AuditBase';

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,914 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,549 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans