2

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.

3

To understand Transaction Log architecture you have few good articles

  1. Understanding Logging and recovery in SQL Server
  2. Transaction Log physical architecture

I would ask you to take some time and read these articles specially the first one.

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.

No you cannot. In simple recovery the space held by committed transaction is re-utilized in transaction log file. When transaction commits checkpoint truncates the logs and when log is truncated the space used by previous transaction will now be re-utilized by new one so log file will not have to grow instead use the space already there. The problem here is long running transaction you must focus on it, this is holding log hostage and not allowing it to truncate there by increasing space.

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?

Yes its recommended not shrink log file. You need to make sure you do not run any transaction which does huge data manipulations. You can do whatever data manipulation you like but make sure amount of rows this manipulation is dealing is not too big. Log file just reacts to what you are running so you have control in it. Make sure after each data manipulation you manually run checkpoints

SQL Server will never shrink data and log files by itself for that you have DBCC SHRINKFILE command.

What is the best practice to deal with log file size?

The article How to set Log file size will help you set optimum value. Basically you have to spend some time looking at transaction log growth and see how much it grows in a day or week. Currently as per your previous question the autogrowth and initial size of both data and log files are not good. You must refer to to this article to set correct autogrowth setting

  • Thank you for removing my wrong assumption about log file entries. I use to think that log contains entries for those transaction also which are completed. – Zerotoinfinity May 7 '15 at 13:45
  • Truncated logs are present there UNLESS new transaction comes and uses the space to write its own information. On busy OLTP server assuming normal scenario transaction log is continuosly truncated and reutalized assuming you have simple recovery. But at any given time if you are lucky you can even read information for transaction, which is truncated but space is not reutalized, from log file using TP log reader tool. – Shanky May 7 '15 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.