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.

Community
  • 1
Zerotoinfinity
  • 761
  • 3
  • 13
  • 27

1 Answers1

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

Shanky
  • 19,148
  • 4
  • 37
  • 58