We run AEM Forms 6.4 on Windows 2016 Server, with the database on MS SQL Server. We only use the Document Security module. The database is very large - we have ~ 800,000 security policies (EDCPOLICYENTITY table) and ~ 100,000,000 secured documents (EDCLICENSEENTITY and EDCREVOKATIONENTITY tables).
On a regular basis (maybe once a month or so) we see AEM Forms starting some activity which creates a huge load on the database. SQL Server disk queue jumps from 0.1 to 500 - 1,000 range. SQL Profiler catches the following long running queries:
declare @p1 int set @p1=1073741841 declare @p2 int set @p2=180150081 declare @p7 int set @p7=10000 exec sp_cursorprepexec @p1 output,@p2 output,N'@P0 bigint,@P1 int',N'select top 10000 * from EDCPOLICYENTITY
where (EDCPOLICYENTITY.sequenceNumber > @P0 and EDCPOLICYENTITY.cacheLocalVersion > @P1)
order by EDCPOLICYENTITY.sequenceNumber ASC ',4104,8193,@p7 output,0,1 select @p1, @p2, @p7
declare @p1 int set @p1=1073741843 declare @p2 int set @p2=180150195 declare @p7 int set @p7=3762 exec sp_cursorprepexec @p1 output,@p2 output,N'@P0 bigint,@P1 int',N'select top 10000 * from EDCLICENSEENTITY
where (EDCLICENSEENTITY.sequenceNumber > @P0 and EDCLICENSEENTITY.version > @P1)
order by EDCLICENSEENTITY.sequenceNumber ASC ',4104,8193,@p7 output,0,1 select @p1, @p2, @p7
Thanks workflowuser, I already did - twice. First time I was told that this is how the product behaves and it cannot be changed. They could not even explain what causes this. Second time I was told that it's caused by key rollover, and they suggested to increase the rollover frequency. As I said above, we now have it set at 9,999 days, but the issue still happens. Any ideas how to stop this activity would be appreciated. Is there some internal process (scheduler) I can stop?
WASIL Thank you for the quick reply. Our key rollover frequency is set to 9,999 days (~ 27 years), but we still see the above activity on a regular basis. Any help on how to stop it would be really appreciated.