AksenovAO

PIFD database transaction log growth

Discussion created by AksenovAO on Sep 20, 2019

Hello.

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 (2.10.0.8628)

HA: NLB

AF Elements Count: 22798

Event Frames Count: 191746

Analysis Count: 3487

Analytics Version: 2018 SP2 (2.10.5.9050)

 

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:

Attachments

Outcomes