Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
peter_turner
Partner - Specialist
Partner - Specialist

Does QV effect SQL transaction log size?

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.

10 Replies
Not applicable

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