hi,
our log file grew big (50 GB) at night when the batch list ran at night. causing AX to crash and stayed in recovery mode for at least an hour or two. it just happened recently. i shrunk it, but it grew big again after the batch list ran. The same batch list has been ran for a long time. i do not know why it is happening in the past two days, and i dont know why. i have not figured it out yet. Has anybody had this happened to your AX before ? Please give me a hint if you know. Thank you.
Hi!
What’s the Recovery Model? What’s the “Auto Growth” setting for the TLog? If the rate is too big maybe this causes the “oversize”. And: did you run any Index Rebuild/Defragmentation? This could also have huge impact on the TLog.
Regards,
Jörg
Depending on the size of your database, 50GB could be a very acceptable size for your log file. You need to do some research about what the different database files are for, what their purpose is. Then investigate how to set up proper databae maintenance, focusing at first on your backup strategy, in relation to your recovery model. When you learn about these topics, you will understand what happens, why the file keeps growing, and what you can do about it. Open Books Online (Start, All programs, SQL Server, Documentation and Tutorials, Books Online), which is the most comprehensive resource library for SQL Server. There is offline help, online help, community links, and much more. In BOL you will find lots of information about transaction logs, backup strategy, recovery model.
Simply shrinking them is not the right approach, as you have seen for yourself they just grow back again. Continuously shrinking them will result in a heavily fragmented log file, which in itself will cause performance problems. What you need to do is determine what is a stable log file size and keep it there, taking into consideration your recovery model, your backup strategy, and your transaction volume. If you need to have a 50GB log file, because you have a batch proces that generates that much data, then you’re going to simply have to accept that you need to keep it at that level. This is not necessarily a bad thing.
hi Jorg,
the recovery model is Simple. would it be best to change to Full ? The auto growth somehow got set to 10% — this might be the problem. Also, should i turn off the auto growth and set it to a fixed size ?
test
Hi,
To control you log file we need to do follwoing
Schedule the backup of log file for every 1/2 hours .
On Weekly basis Truncate the log file.
Defragmentation
Above things will surely keep your log file in control.
Hope this will surely help you
Regards
Amol
Well, with SIMPLE Recovery the committed data should be automatically truncated from the TLog to keep it small. On the other hand, with SIMPLE you cannot create TLog Backups, thus with FULL Recovery you could/have to do this to minimize the potential loss of data in case of disaster recovery …
Ideally the size of a SQL DB should be monitored and adjusted by some administrator frequently; a DB should have about 20 to 25 percent free space. The “Auto Growth” should just provide some kind of “emergency expansion” and should be set to a fix value, e.g. 100 to 250 MB (depending on disk-subsystem).
So still the question is what causes your TLog growth … hence, it must be some super-size transaction which is modifying a lot of data … or some system maintenance like ALTER INDEX etc. …
Thanks for explaining. i think i have found the areas that cause the problem. Thanks again.
Thank you Amol.
Thank you Daniel
Could you please share this solution with us? What finally caused the problem? Could you solve it? How?
Hi SPN-AX,
I have the same problem as your. I am trying to install IDMF ( Intelligent Data Management Framework) to clean up and manage data.
Can you tell me your finding? What is your solution?
thanks
Minh
hi Minh,
my apology for replying late. i am not sure what your situation is, but mine was the user/coding error. a wrong parameter was picked by a user and our codes ( was not written by me) did not not take care of that case. it caused the loop to run forever. i traced it down and fixed it. if that happened recently, you should check your batch list one by one or whatever you are running recently. Also, check the AutoGrowth of the log file to see if it is where you want it to be.
does your log file grow big recently ? do you run any batch list ?