We have a problem with the PIFD database transaction log growth.
Two weeks ago PIFD database transaction log has started to overfill very fast. We have Limit 10240 Mb set for transaction log growth. We hit this limit in 6-8 hours.
We made no big changes in existing AF Databases and didn’t add new databases in AF.
What can we do to find a reason of transaction log rapid growth?
AF Version: 2018 (220.127.116.1128)
AF Elements Count: 22798
Event Frames Count: 191746
Analysis Count: 3487
Analytics Version: 2018 SP2 (18.104.22.16850)
SQL Version: 2012 SP2 (11.0.5058.0)
HA: Always on
We checked up the SQL Query execute frequency, but didn’t find which SQL Query makes an impact on transaction log.
Most frequent query is :
UPDATE [dbo].[AFElementVersion] WITH ( UPDLOCK, HOLDLOCK )
SET [IneffectiveDate] = @NewInEffectiveDate,
[revision] = [revision] + 1,
[changedby] = @user,
[LUPD] = @dt
WHERE [ID] = @ID
AND [IneffectiveDate] <> @NewInEffectiveDate ;
Statistics of execution from activity monitor: