0

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

Improve this question
6
  • How often are you doing transaction log backups? A log won't shrink after it get's larger, so if you had a huge amount of transactions that were in the log, then the log will just keep getting bigger till the next back up.
    – Thom A
    Mar 3, 2020 at 9:16
  • I dont backup from transaction log
    – hmahdavi
    Mar 3, 2020 at 9:19
  • In the Full recovery, model LogFiles contains every transaction details as recoverable when you convert it to simple transaction details that will be pointed as unused in the LOGINFO table. So the shrink process truncates these pages.
    – Esat Erkec
    Mar 3, 2020 at 9:41
  • 2
    "I dont backup from transaction log" Well, there's your problem.
    – Thom A
    Mar 3, 2020 at 9:46
  • 1
    To give more information about what Larnu says. The transaction log back-up will clear the transaction log. Then you have to plan this type of back-up for example every hours to avoid the huge transaction log file. And by doing this you won't have to shrink the transaction log again because it will not become huge. The interval between 2 transactions log back-up depend on your database usage. learn.microsoft.com/en-us/sql/relational-databases/…
    – Carbon 4horse
    Mar 3, 2020 at 10:15

1 Answer 1

Reset to default
1

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!

Improve this answer

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