We have a DB that save a transaction log each 15 min.
the problem is that this log become huge(around 1GB each 15 min) and this because of 1 query.
This query is executing in average each 12 seconds and truncating and filling some temporary tables with huge amount of data.
My question is : is it possible to avoid this query to write in transaction LOG so we can minimize the log file size.
i think you can change the database recovery mode so that it does not write to log
My recommendation is provide adequate disc space so the log will have enough space and stop shrinking.
Why is that code truncating and filling 'temporary' tables with huge amount of data?
If the code was actually using real temp tables - loading those tables would not be logged in the transaction log of the user database. They would be logged in the transaction log for tempdb - and the space reused as soon as the transaction is complete.
Review and optimize the code...