-- 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 |
This comment has been minimized.
This comment has been minimized.
Hi. Yes. Backup is needed. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
I see that you are dropping the AuditBase table with a table backup ? is the Audit history not needed ?