Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- Change DB
use [org_MSCRM]
-- 1. PartitionCreate
-- Drop any existing partition function and scheme
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'AuditPScheme')
BEGIN
print 'Dropping Partition Scheme AuditPScheme'
DROP PARTITION SCHEME [AuditPScheme]
END
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'AuditPFN')
BEGIN
print 'Dropping Partition Function AuditPFN'
DROP PARTITION FUNCTION [AuditPFN]
END
-- Create partition fucntion
DECLARE @currentDate DATETIME;
SET @currentDate = GETDATE();
DECLARE @firstPartition DATETIME;
DECLARE @secondPartition DATETIME;
SET @firstPartition = DATEADD(ms, -3, (DATEADD(qq, 1, DATEADD(qq, DATEDIFF(qq, 0, @currentDate), 0))));
SET @secondPartition = DATEADD(ms, -3, (DATEADD(qq, 2, DATEADD(qq, DATEDIFF(qq, 0, @currentDate), 0))));
print 'Current Date = ' + CONVERT(varchar(50), @currentDate)
print 'First Partition Date = ' + CONVERT(varchar(50), @firstPartition)
print 'Second Partition Date = ' + CONVERT(varchar(50), @secondPartition)
CREATE PARTITION FUNCTION AuditPFN(datetime)
AS RANGE LEFT FOR VALUES (@firstPartition, @secondPartition)
-- Create partition scheme
CREATE PARTITION SCHEME AuditPScheme AS PARTITION AuditPFN ALL TO ([PRIMARY])
-- 2. AuditTableCreate
-- Drop Audit table
IF EXISTS (SELECT * FROM sys.tables WHERE Name = 'AuditBase')
BEGIN
print 'Dropping Audit table AuditBase'
DROP Table AuditBase
END
-- Create Audit table on AuditScheme
CREATE TABLE [dbo].[AuditBase](
[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,
) ON AuditPScheme (CreatedOn)
-- 3. IndexCreateOrRecreatePartition
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_PrimaryKey_Audit_Primary' AND OBJECT_NAME(object_id) = 'AuditBase')
BEGIN
DROP INDEX [dbo].[AuditBase].[ndx_PrimaryKey_Audit_Primary]
END
-- Create an index on the primary so that select top N query works faster
CREATE UNIQUE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit_Primary] ON [dbo].[AuditBase]
(
[CreatedOn] DESC,
[AuditId] DESC
) ON [PRIMARY]
IF EXISTS (SELECT name FROM sys.partition_schemes WHERE name='AuditPScheme')
BEGIN
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'cndx_PrimaryKey_Audit' AND OBJECT_NAME(object_id) = 'AuditBase')
BEGIN
DROP INDEX [dbo].[AuditBase].[cndx_PrimaryKey_Audit]
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_PrimaryKey_Audit' AND OBJECT_NAME(object_id) = 'AuditBase')
BEGIN
DROP INDEX [dbo].[AuditBase].[ndx_PrimaryKey_Audit]
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_ObjectId' AND OBJECT_NAME(object_id) = 'AuditBase')
BEGIN
DROP INDEX [dbo].[AuditBase].[ndx_ObjectId]
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_UserId' AND OBJECT_NAME(object_id) = 'AuditBase')
BEGIN
DROP INDEX [dbo].[AuditBase].[ndx_UserId]
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'fndx_ObjectTypeCode' AND OBJECT_NAME(object_id) = 'AuditBase')
BEGIN
DROP INDEX [dbo].[AuditBase].[fndx_ObjectTypeCode]
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'fndx_Action' AND OBJECT_NAME(object_id) = 'AuditBase')
BEGIN
DROP INDEX [dbo].[AuditBase].[fndx_Action]
END
CREATE UNIQUE CLUSTERED INDEX [cndx_PrimaryKey_Audit] ON [dbo].[AuditBase]
(
[CreatedOn] DESC,
[AuditId] DESC
) ON AuditPScheme (CreatedOn)
CREATE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit] ON [dbo].[AuditBase]
(
[AuditId] ASC
) ON AuditPScheme (CreatedOn)
CREATE NONCLUSTERED INDEX [ndx_ObjectId] ON [dbo].[AuditBase]
(
[ObjectId] ASC
) ON AuditPScheme (CreatedOn)
CREATE NONCLUSTERED INDEX [ndx_UserId] ON [dbo].[AuditBase]
(
[UserId] ASC
) ON AuditPScheme (CreatedOn)
CREATE NONCLUSTERED INDEX [fndx_ObjectTypeCode] ON [dbo].[AuditBase]
(
[ObjectTypeCode] ASC,
[Action] ASC
)
WHERE ([ObjectTypeCode] IS NOT NULL)
ON AuditPScheme (CreatedOn)
END
@omranabd

This comment has been minimized.

Copy link

omranabd commented Sep 25, 2016

I see that you are dropping the AuditBase table with a table backup ? is the Audit history not needed ?

@sergeytunnik

This comment has been minimized.

Copy link
Owner Author

sergeytunnik commented Oct 10, 2016

Hi. Yes. Backup is needed.
select * into AuditBase_back from AuditBase
Full post with detailed explanation can be found here: https://tunnik.name/crm-audit-log-and-sql-partitioning/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.