0

My database has two log files. Their sizes are growing very fast. My backup sizes are becoming massive. I know that when I take full backup of my database, the log files are emptied but it is not. I can't control it.How can I control log growth?

1

Full backup doesn't clear the logs, I suppose your database is not in simple recovery mode as logs are increasing and its filling up rapidly. Do you take transnational log backup anytime in a day or week?

Ideally Full backup should be taken every day, Differential backup few times in a day and log backup few times in an hour however it also depends upon RPO and RTO.

One thing to note here is transaction log backup helps you in storing active part of transaction as illustrated below:

A transaction log backup allows you to backup the active part of the transaction log. So after you issue a "Full" or "Differential" backup the transaction log backup will have any transactions that were created after those other backups completed. After the transaction log backup is issued, the space within the transaction log can be reused for other processes. If a transaction log backup is not taken, the transaction log will continue to grow.

Please read recommendation from Microsoft on backup and frequency:

A database administrator typically creates a full database backup occasionally, such as weekly, and, optionally, creates a series of differential database backup at a shorter interval, such as daily. Independent of the database backups, the database administrator backs up the transaction log at frequent intervals. For a given type of backup, the optimal interval depends on factors such as the importance of the data, the size of the database, and the workload of the server. For more information about implementing a good strategy, see Recommendations in this topic.

You may read more about RPO and RTO from this link as nicely explained by Mr. Paul Randal.

You may also check best practice for Transaction log autogrowth and other details:

https://www.sqlshack.com/sql-server-transaction-log-administration-best-practices/

  • Thank you very much for your excellent explanation. I will take transactional backup asap – yummit Dec 1 at 13:29
  • @yummit taking a single transaction log backup isn't sufficient. If you intend to keep using the FULL recovery model, you need to be taking them regularly, preferably via a SQL Agent Job. – alroc Dec 1 at 14:34
  • @alroc Thank you very much for the great help. In fact, i didn't know that. Now, i gotta create a job to execute that process. I am so glad you were here .. – yummit Dec 1 at 15:02
  • @yummit save yourself a LOT of trouble and install & schedule the jobs provided by Ola Hallengren's Maintenance Solution. ola.hallengren.com . Once you're taking regular log backups, you'll be in the rare position of being able to safely resize the transaction log to something more reasonable and get some disk space back. – alroc Dec 1 at 15:09
0

I know that when i full backup of my database, the log files are emptied

No, full backup of database "BACKUP DATABASE..." does not clear transaction logs

You need to perform regular (say each 1 minute) transaction log backups "BACKUP LOG... WITH NOINIT" in order to truncate the transaction logs

NO INIT option means all consequent log backup will add record to log backup file (.trn) instead of overwriting its contents. This is needed to be able to restore to a point in time. With NOINIT transaction log will get truncated, but keep attention at the log backup file (.trn) - it will grow.

What you also will need to do is run transaction log backup "BACKUP LOG... WITH INIT" just once right after each full of differential database backup, because transaction log backup file (.trn) will need to be cleared from time to time, too

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