15 Replies Latest reply on Oct 19, 2017 7:17 PM by Floris Zwaard

    AF SQL Database Transaction Log

    ian.d.gore

      Hi All,

       

      I've recently expanded the use of AF Analytics on my system and I'm getting "The transaction log for database 'xxxx' is full" messages being logged and the AF Analytics service seems to hang up.

       

      We're currently investigating the size, etc. of the transaction log for the database, but I'm interested in what causes transactions to be logged in this way on a normally running system which is not being changed as this may affect the way we implement the analytics going forward.

       

      Any tips for optimising Analyses to minimise the impact on the SQL database (eg updates, transactions, etc).  Would be very useful.

        • Re: AF SQL Database Transaction Log
          Rhys Kirk

          Are you writing outputs of analyses to attributes with no Data Reference?

            • Re: AF SQL Database Transaction Log
              ian.d.gore

              Yes,  typically rollup analyses. 

               

              Because the rollup only works on the immediate children of an element, I'm having to rollup to each level in the AF hierarchy and I don't want to keep the intermediate results just the top level one.

                • Re: AF SQL Database Transaction Log
                  ian.d.gore

                  In the past I've written a custom DR to do a multi-level rollup and optimize the rollups by doing more than one attribute at a time.  I was hoping I didn't need to do that with Analyses - but it seems like I might have to.

                  • Re: AF SQL Database Transaction Log
                    Rhys Kirk

                    Writing to an Attribute with no DR causes an impact to SQL Server...if you're doing this en mass then you'll have a bigger impact.

                    If you have AF 2.7 then you can use the new "Analysis" DR which evaluates the Analysis at run time, like the Formula DR.

                      • Re: AF SQL Database Transaction Log
                        Asle Frantzen

                        Rhys Kirk:

                         

                         

                        If you have AF 2.7 then you can use the new "Analysis" DR which evaluates the Analysis at run time, like the Formula DR.

                        Care to elaborate what you mean by this, Rhys Kirk?

                         

                        .a

                          • Re: AF SQL Database Transaction Log
                            Rhys Kirk

                            Create a new Attribute with no DR:

                             

                            Create a new Analysis outputting to that Attribute:

                            Go back to your Attribute, it is now using the Analysis DR:

                             

                            Even if you set a large periodic schedule, like the default 5 minutes, each time you refresh (read) the Attribute value it is re-evaluated by the client like the Formula DR.

                             

                            If you then want to save the history of the analysis you can click on the Attribute in the Analysis and select Save History:

                             

                             

                            A very handy bit of functionality, I'm sure you'll agree Asle Frantzen

                            5 of 5 people found this helpful
                              • Re: AF SQL Database Transaction Log
                                Asle Frantzen

                                Excellent description, Rhys! I had heard rumours of such a functionality but I hadn't seen in yet.

                                 

                                I was doing something similar yesterday, and was using one of my test AF servers. Turns out I was using the one I hadn't upgraded from 2.6 yet. Typical

                                  • Re: AF SQL Database Transaction Log
                                    ian.d.gore

                                    A very nice bit of functionality - pity my client won't be upgrading any time soon

                                     

                                    Looks like I'll be writing a DR then.

                                      • Re: AF SQL Database Transaction Log
                                        TimCarmichael

                                        Tagging the end of the extended chain.

                                        As others have said, if you are doing SOMETHING in AF that modifies data and the values are stored, the database will grow.

                                        If you have the option of altering your SQL Server database maintenance, back up your transaction logs on a frequent basis and then do a full back up at least daily.
                                        It has been a few years since I've done a lot of SQL work, so my skills and recollection are rusty.

                                        We didn't rely on 'built-in' maintenance; we created a custom T-SQL job that accepted parameters to define the backup type (logs, full, etc), shrink/noshrink.

                                        Will a job, we could set the schedule to be executed as often as we needed.

                                         

                                        If that isn't an option, we'd be interesting in the 'generic' details of your DR job.. share what you can without violated company policy.

                                        • Re: AF SQL Database Transaction Log
                                          Asle Frantzen

                                          You're aware of the old Rollup example data reference OSIsoft provided in the "Implementing AF 2.X Data References" white paper, right?

                                           

                                          Three custom DR's with code.

                                           

                                          I think it's this one:

                                          https://techsupport.osisoft.com/Downloads/File/5cbefb97-d253-46dd-b369-f36cda374e47

                                           

                                          Might be a good starting point.

                                            • Re: AF SQL Database Transaction Log
                                              ian.d.gore

                                              Yes, that's what I based my original DR on.  

                                               

                                              When you want to count the number of occurrences that child elements are in a particular state (eg a valve is open/closed/moving and you want to know the number that are closed, the number that are open and the number that are moving) then rollup analyses become really inefficient - you end up having analyses to set attributes in the end elements to count and then rollups to count those attributes and, if there are levels in your hierarchy, more rollups to rollup the rollups, etc., etc.  

                                               

                                              It's just much more efficient to write a custom DR to examine each end element and count each state.   Then store the result in an array.

                                               

                                              (Trouble is then that AF Analyses can't access arrays, so you have to write a DR to unpack the array )

                                                • Re: AF SQL Database Transaction Log
                                                  Asle Frantzen

                                                  I've been down that Rollup DR route myself, in a 30k element system, and I do not want to go back there - that's for sure.

                                                   

                                                  I believe that if I was forced to do this kind of rollup without analysis functionality today I would have opted for a server side solution in a third party software. For example a SQL Server solution which'd scan the AF hierarchy in a SQL Server Agent job, and then just have table lookups to collect the values back into AF.

                                                   

                                                  When you've stared at an AF element for 90+ seconds and it still says "Retrieving the data reference values. Please wait..." you need to optimize the way you're doing things

                                                  • Re: AF SQL Database Transaction Log
                                                    Roger Palmen

                                                    Be careful with this type of custom DR's though. I've had serious issues with this RollUp example...

                                                     

                                                    I'd say, let's go back to the original problem: SQLserver is running out of space for the TXN logs. That is an SQL problem to solve not an AF issue.

                                                    I do agree this is partly caused by Analytics writing to AF attributes, but even that can be solved by stock configuration as Rhys pointed out. And even there you could opt to just use a PI Point and not archive values.

                                  • Re: AF SQL Database Transaction Log
                                    jainc_paresh

                                    Hello,

                                     

                                    One thing I would like to confirm how periodic are you taking backup for your AF database. The log size could also grow if the periodic full backup and transaction log backup is not taken.

                                     

                                    If not set log size with "Enable Autogrowth" for PIFD  databse, then set it to  "Enable Autogrowth" in SQL server

                                     

                                     

                                    For now, to reduce the log file size, you will have to manually take transaction log backup (may be 2-3 times), then shrink the log file.

                                      • Re: AF SQL Database Transaction Log
                                        Floris Zwaard

                                        >For now, to reduce the log file size, you will have to manually take transaction log backup (may be 2-3 times), then shrink the log file.

                                        I guess this only accounts for the, in the AF documentation recommended, FULL recovery model. In the simple recovery model the transaction log db should be automatically managed.

                                        But for some reason it just grows and grows. In some cases quite fast(700mb per day) even though there are nightly full backups made!

                                        For now the only solution I found is taking the database offline, moving the _log.lbf database file to another place and bring the  database online again. which gives the _log db file its initial size starting with a +-0% utilization.