You’re offline. This is a read only version of the page.
Announcements
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.
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
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
#1
André Arnaud de Cal...
291,359
Super User 2024 Season 2
#2
Martin Dráb
230,370
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
