I have posted the first part of my question here & I don't want to flood my question with a loads of information and would force people to alter their answers for my each question.
I think I am lacking the knowledge about how logs works, so I would appreciate if anyone can redirect me to tutorial or book which can tells me deep about it. However, I am confused about
- I understand that I get error because my log file was filled when SQL was either trying to run an active transaction or was trying to create checkpoints.
but I have large enough space for log file (i.e. 200 GB); so is there any way I can remove entries from log file for all transaction other than active transaction. Because at max my transaction will not exceed 10 GB at any instance.
It is recommend not to shrink log file But then how can I ensure that my future transactions (i.e queries) will run smoothly? Does SQL Server shrink/flush/delete data from log by itself?
What is the best practice to deal with log file size?
I already have 200GB allocated for log file and I can ask for more. Isn't that enough? and if I keep increasing my drive size then some other day I will be on the same position where I am today.