Answered by:
How to restore partitioning on SQL server Enterprise Edition

Question
-
Hello,
I suspect that my current Dynamics CRM 2011 organization database was migrated from a Standard SQL server to an Enterprise SQL server. Because, when moving from Standard to Enterprise edition, partitioning was not applied to the AuditBase table.
The CRM server is running Update Rollup 14.
Do you know how to restore partitioning on Audit table?
Thank you very much.
Thomas Canaple
Tuesday, May 13, 2014 11:40 AM
Answers
-
This issue has been identified and fixed in rollup 7. You already have rollup 14, so not sure why is that happened.
1. Go to Settings->System->Auditing, Check Audit Summary View and Audit Log Management, see if it is partitioned.
2. If not, run the sql
3. Go to Audit Log Management, Check the first partition's TO value, and set system date later than that, run iisreset in command window
4. Create some contacts and do some CRUD operations
5. Go to Audit Log Management, delete the first partition
6. Go to Audit Summary ViewBefore doing that, remember to backup your database.
-------------SQL--------------
BEGIN TRY
BEGIN TRAN
DECLARE @engineEdition as sql_variant
SELECT @engineEdition = SERVERPROPERTY('EngineEdition')IF @engineEdition <> 3
BEGIN
RAISERROR 34567 'Sql Server Edition should be Enterprise to proceed.'
END-- Drop 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]
ENDIF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'AuditPFN')
BEGIN
PRINT 'Dropping Partition Function AuditPFN...'
DROP PARTITION FUNCTION [AuditPFN]
ENDPRINT 'Creating partition function and scheme...'
-- Create partition function
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))));
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])PRINT 'Creating indexes on AuditBase...'
-- Create all neccesary indexes on AuditBase
-- Creating clustered index on AuditPScheme will affectively partition the table
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
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]
ENDIF EXISTS (SELECT * FROM sys.indexes WHERE name = 'fndx_Action' AND OBJECT_NAME(object_id) = 'AuditBase')
BEGIN
DROP INDEX [dbo].[AuditBase].[fndx_Action]
ENDIF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_SystemManaged_Audit' AND OBJECT_NAME(object_id) = 'AuditBase')
BEGIN
DROP INDEX [dbo].[AuditBase].[ndx_SystemManaged_Audit]
UPDATE EntityIndex
SET RecreateIndex = 1
WHERE Name = 'ndx_SystemManaged_Audit'
ENDCREATE UNIQUE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit_Primary] ON [dbo].[AuditBase]
(
[CreatedOn] DESC,
[AuditId] DESC
) ON [PRIMARY]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
)
WHERE ([ObjectTypeCode] IS NOT NULL)
ON AuditPScheme (CreatedOn)COMMIT TRAN
PRINT 'Partitioning of AuditBase is successfull.'
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'EXECUTION FAILED: ' + ERROR_MESSAGE()
END CATCH- Marked as answer by Thomas Canaple Friday, May 16, 2014 8:52 AM
Wednesday, May 14, 2014 2:39 AM
All replies
-
Hi,
you can remove the Audit Partition described in this article: KB2567984. Hope this helps!
Kind regards
Martin
Tuesday, May 13, 2014 6:09 PM -
This issue has been identified and fixed in rollup 7. You already have rollup 14, so not sure why is that happened.
1. Go to Settings->System->Auditing, Check Audit Summary View and Audit Log Management, see if it is partitioned.
2. If not, run the sql
3. Go to Audit Log Management, Check the first partition's TO value, and set system date later than that, run iisreset in command window
4. Create some contacts and do some CRUD operations
5. Go to Audit Log Management, delete the first partition
6. Go to Audit Summary ViewBefore doing that, remember to backup your database.
-------------SQL--------------
BEGIN TRY
BEGIN TRAN
DECLARE @engineEdition as sql_variant
SELECT @engineEdition = SERVERPROPERTY('EngineEdition')IF @engineEdition <> 3
BEGIN
RAISERROR 34567 'Sql Server Edition should be Enterprise to proceed.'
END-- Drop 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]
ENDIF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'AuditPFN')
BEGIN
PRINT 'Dropping Partition Function AuditPFN...'
DROP PARTITION FUNCTION [AuditPFN]
ENDPRINT 'Creating partition function and scheme...'
-- Create partition function
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))));
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])PRINT 'Creating indexes on AuditBase...'
-- Create all neccesary indexes on AuditBase
-- Creating clustered index on AuditPScheme will affectively partition the table
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
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]
ENDIF EXISTS (SELECT * FROM sys.indexes WHERE name = 'fndx_Action' AND OBJECT_NAME(object_id) = 'AuditBase')
BEGIN
DROP INDEX [dbo].[AuditBase].[fndx_Action]
ENDIF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ndx_SystemManaged_Audit' AND OBJECT_NAME(object_id) = 'AuditBase')
BEGIN
DROP INDEX [dbo].[AuditBase].[ndx_SystemManaged_Audit]
UPDATE EntityIndex
SET RecreateIndex = 1
WHERE Name = 'ndx_SystemManaged_Audit'
ENDCREATE UNIQUE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit_Primary] ON [dbo].[AuditBase]
(
[CreatedOn] DESC,
[AuditId] DESC
) ON [PRIMARY]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
)
WHERE ([ObjectTypeCode] IS NOT NULL)
ON AuditPScheme (CreatedOn)COMMIT TRAN
PRINT 'Partitioning of AuditBase is successfull.'
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'EXECUTION FAILED: ' + ERROR_MESSAGE()
END CATCH- Marked as answer by Thomas Canaple Friday, May 16, 2014 8:52 AM
Wednesday, May 14, 2014 2:39 AM -
Thank you Wilson, I will try your solution.
I think that this situation occurred before we set-up Update Rollup 7, but was detected recently.
Wednesday, May 14, 2014 9:53 AM -
Thank you again Wilson. It works!
Because we authenticate using ADFS, I cannot change server time and must wait for the following partition to be created.
Friday, May 16, 2014 8:51 AM