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

hmahdavi
  • 107
  • 4
  • 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 03 '20 at 09:16
  • I dont backup from transaction log – hmahdavi Mar 03 '20 at 09: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 03 '20 at 09:41
  • 2
    "I dont backup from transaction log" Well, there's your problem. – Thom A Mar 03 '20 at 09: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. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/transaction-log-backups-sql-server?view=sql-server-ver15#Recommendations – Carbon 4horse Mar 03 '20 at 10:15
  • You don't need to bother with transaction log backups in the SIMPLE recovery model but your only recovery option is from full (and optionally differential) backup, meaning all changes made since the backup will be lost if you need to restore. The FULL (or BULK_LOGGED) model along with regular log backups will mitigate data loss and backing up the log will keep the log file size reasonable. Regardless, make sure you have a recovery plan and test it. – Dan Guzman Mar 03 '20 at 11:24

1 Answers1

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!