This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
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
I see that you are dropping the AuditBase table with a table backup ? is the Audit history not needed ?