We have an industrial program. For a device that is running 24 hours a day. The database is constantly used. What I noticed today after 4 months: The database log file size was 80 Gig !
Query execution speeds were slower. Shrink did not reduce the volume of logs. With my search I set the Recovery Model to Simple, and then I did the Shrink . Logs volume reduced to 1 MB !
Recovery model set to Full.
But why did this happen? And how can I stop it?
If you need more info tell me to add it
Here's a simple analogy as to why transaction logs vs simple backups are vastly different sizes.
Imagine you have the number 10. Each day for the next week you add 1 to it. This gives you the number 17 after a week.
If you're recording every single transaction in a complex logging scenario you're essentially storing:
10+1+1+1+1+1+1+1
Although in this case each +1 is actually dozens of lines of code!
Wouldn't it be easier just to store the result (17). Now obviously this is less flexible and won't allow you to roll-back cleverly if things go wrong - but that's the fundamental trade-off here!