10 Replies Latest reply: Apr 8, 2010 7:17 PM by Tomas Nilsson RSS

    Does QV effect SQL transaction log size?

    Peter Turner

       

      Hello All!

      Q: Does QV effect SQL transaction log size?

      I've seen on 2 machines that the SQL transaction log size had grown to its maximum size, and was effecting the SQL database's ability to store data.
      As QV should only be reading data (with SQL select statements) I'm unsure why this might happen.
      Has anyone had similar behaviour?

      I know a possible solution is to perform database backups as this will clear the transaction log, but i wanted to know why the situation occurred in the first place.

      Thanks,
      Peter.

        • Does QV effect SQL transaction log size?
          John Witherspoon

          If you're logging reads, then yes, QlikView would affect the SQL transaction log size. I believe that in our environment, we only log insert/update/delete, so at least in our environment, that isn't an issue. It sounds like you don't expect that to be an issue in your environment either.

          Another thing you may need is FOR FETCH ONLY in your SQL statements in QlikView. In our system, if you don't specify for fetch only, it will lock the records you're reading on the assumption that you may follow up the read with an update. On top of it being a bad idea to take locks to simply read data, it at least seems reasonable that the locks would push the data into your SQL transaction log.

          • Does QV effect SQL transaction log size?
            Chris Cammers

            In addition to working with our BI tools I occasionally wear the SQL dba hat. So I think I will throw in my two cents.

             

            Depending on the recovery model you are using on your database you must perform regular log and database backups to manage the size of the log.

            If you are in a Simple Recovery mode then the log will manage itself but now that your log is large you will have to shrink it to get it back to a more "normal" size.

            If you are in Full Recovery mode then you must back up your database and logs in order to manage the log size. Part of the backup process is to remove transactions from the log and move them into the data file.

            Generally speaking I would not worry about reads from Qlikview causing the log to grow, what I wonder is do you have maintenance plans running on a regular basis and how much does your data change each day. That will give me the best answer to how your log file got so big. So unless you are dumping this database and reloading from scratch every night or it is read-only then I would make sure to get a maintenance plan running. Eventually your log will fill the disk and then you won't be able to do anything.

            Like I said dba is one of the hats I wear so I'm not the necessarily an expert but I've dealt with this situation many time.

             

            Thanks

            Chris

             

             

             

             

            • Does QV effect SQL transaction log size?

              For Microsoft SQL Server the short and simple answer is: No.

              The more complex answer is; it depends. Reading in SQL Server doesn't generate log. Only writing (=inserting or updating). It is possible to write back to the database using QlikView, but I'd guess that's not the case.

              Each time a update/insert is performed, the logfile is written. It will grow until one of the following occours:

              1) A backup is done.

              ... but even when a backup has been done, the logfile is still in the same size. SQL leaves the file as is and only moves the write pointer to the beginning of the file. It's a really smart strategy, if you have an average of 100Mb of log each 24h, the logfile will grow and then eventually stay at some size (let's say 150Mb). There is a penalty cost of extending the size of the logfile, which is avoided by this strategy.

              b) The log is discarded

              ... The log can be discarded in many ways, but I wouldn't recommend any of them for a production system. If the system is important, keep a backup of the database and the logs (this is known as "Full recovery").

              If the system doesn't have use for backups/logs, use "simple backup" (also known as "Truncate log on checkpoint"), which will move back the pointer to the beginning of the log file after each completed insert/update operation. In essense, the log file will be overwritten over and over (and not appended). In this case, the log file will grow up to the size of the largest insert/update operation you do and then stay there.

              I hope I've shed some light, and that the information is accurate. Quite some years has passed since I worked with SQL...

              /Tomas