Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics CRM forum
Answered

Microsoft Dynamics 365 Audit Partitions Creation Job

Posted on by Microsoft Employee

Hello,

We have Microsoft Dynamics 365 Version 1612 (8.2.2.112) (DB 8.2.2.112) on-premises, with enabled auditing, the audit is partitioned in quarter base.

since a couple of quarters, the system did not created new quarters, I have checked the job (Create Audit Partition) using the job editor tool and found an error related to SQL exception.

the question is about that job location, is it DB job or Dynamics system job, or any thing else, is it possible to see the code of that job and change it ?

Thanks.

Mohammad.

  • Suggested answer
    RE: Microsoft Dynamics 365 Audit Partitions Creation Job

    Hi,

    The below query would be much safer to run-

    if exists (select * from sys.objects where object_id = object_id(N'[dbo].[p_CreateNextAuditPartition]') and type in (N'P', N'SP'))

    begin;

              drop procedure dbo.p_CreateNextAuditPartition;

    end;

    go

    create procedure [dbo].[p_CreateNextAuditPartition](

              @dtToUseInCreatePartition datetime = null  

    )

    as

    begin;

              if (@dtToUseInCreatePartition is null)

              begin;

                         set @dtToUseInCreatePartition = getutcdate();

              end;

              -- First check whether Partition Mgmt enabled for this SQL server

              -- Value of Engine edition for Sql Enterprise edition is 3 and for Sql Azure edition is 5

              declare @edition int = convert(int, (select serverproperty('EngineEdition')));

              if (@edition = 3 or @edition = 5)

              begin;

                         -- Validate the Partition function exists                    

                         if not exists(select 1 from sys.partition_functions where name = 'AuditPFN')

                         begin;

                                     RAISERROR('Partition function AuditPFN Not found. Exiting...', 16, 1);                                

                                     return 1;

                         end;

                         declare @lastPartitionStartDate  datetime;

                         select @lastPartitionStartDate = convert(datetime, max(prv.value))

                         from sys.dm_db_partition_stats pst

                                     left outer join sys.partition_range_values prv on prv.boundary_id = pst.partition_number

                         where object_id = object_id('AuditBase')

                                     and index_id in (0,1) and prv.value is not null;

                         declare @nextQtrEnd datetime;

                         select @nextQtrEnd = dateadd(ms, -3, dateadd(qq, datediff(qq, 0, @dtToUseInCreatePartition) + 2, 0));

                         -- Check if the partition already exists for @nextQtrEnd, then exit

                         declare @errMessage varchar(500);

                         if exists(select 1 from sys.partition_range_values where value = @nextQtrEnd)

                         begin;

                                     set @errMessage = 'Partition already exists for ' + CAST(@nextQtrEnd as varchar(30)) + '. Exiting...';

                                     print @errMessage;                                                          

                                     return 2;

                         end;

                         -- Create a new partition only if there is no partition bounday within 2 months of the new date

                         -- this is basically to check if someone has already created the corresponding partition

                         -- We are checking with 2, so that change of timezone will not result in false positives

                         if abs(datediff(mm, @lastPartitionStartDate, @nextQtrEnd )) >= 2

                         begin;

                                     alter partition scheme AuditPScheme next used [PRIMARY];

                                     alter partition function AuditPFN() split range (@nextQtrEnd);

                                     return 0;

                         end;

                         else

                         begin;

                                     set @errMessage = 'Partition already exists within 2 months of ' + CAST(@nextQtrEnd as varchar(30)) + '. Exiting...';

                                     print @errMessage;                                                          

                                     return 3;

                         end;

              end;

              else

              begin;

                         print 'Not SQL Azure or SQL Enterprise Edition. Exiting...';

                         return 5;

              end;

    end;

    go

    NOTE:  Perform it in dark hours

    Hope this helps!

    Regards,

    Venkatesh N

  • Suggested answer
    Ozarour Profile Picture
    Ozarour 630 on at
    RE: Microsoft Dynamics 365 Audit Partitions Creation Job

    Hi Mohammad,

    Dynamics CRM Create Audit Partition Job triggers a stored procedure called p_CreateNextAuditPartition as shown in below SS.

    All CRM jobs can be found in MSCRM_Config DB table ScaleGroupOrganizationMaintenanceJobs

    pastedimage1642919840130v1.png

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,532 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,501 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans