3

I noticed that the first transaction log backup of the day, at 0700 is like 3GB. We run a full backup each night at 22:00. We are a 9-5 shop so there is no real activity overnight. We take TLog backups hourly during the day and they average around 30 MB. How can I find out why this first log backup is so big? Thanks!

2
  • The ERRORLOG of the SQL Server instance might provide additional details. Have a look. Then check the SQL Server Agent logs. Please add this information to your question.
    – John K. N.
    Oct 6, 2017 at 21:15
  • 2
    "We are a 9-5 shop so there is no real activity overnight." - REALLY? No backup taken? No database maintenance automatically scheduled? Reorganizations is the typical culprit of "what is the db doing when noone is working" because someone thought it is a good idea.
    – TomTom
    Oct 9, 2017 at 15:39

3 Answers 3

7

That depends on what is happening in the night.

  • If you have additional maintenance jobs (index rebuilds, index reorganizations, statistics updates) then you possibly have a lot of changes occurring.
  • If you are also importing large amounts of data, then that is another possible source of changes.
  • Deleting data is also a modification and will be logged.

A database that is truly dormant will normally not have huge transaction log backups.

You may also want to consider either taking a second full backup and a transaction log backup after the nightly maintenance, and use that backup as your base for the day's log backups solely to avoid the massive post-maintenance log file, or to just postpone the full backup and subsequent transaction log backups till after the maintenance.

6
  • 1
    Index rebuilds and reorganizations are especially log intensive. You may want to take a second full backup after nightly maintenance, and use that as your base for the day's log backups solely to avoid that massive post-maintenance log file. Oct 6, 2017 at 23:27
  • 1
    @Laughing Vergil >>>You may want to take a second full backup after nightly maintenance, and use that as your base for the day's log backups solely to avoid that massive post-maintenance log file.<<< You can certainly take a second full backup after maintenance, but IT WILL NOT CHANGE LOG FILE SIZE, as well as it will not change the size of the next log backup.
    – sepupic
    Oct 9, 2017 at 8:39
  • You may actually not want to do index reorgs every night. I mean, seriously - today (SSD times) this will bring little. Only reorg when you need. I still have to see a database where every night is sensible.
    – TomTom
    Oct 9, 2017 at 15:40
  • Thanks for the feedback. We do run an IndexOptimize (Ola's) Sun-Thurs. and I do see the correlation with the increased log size. The nights where we don't run this, the log file is not that big the following morning so that must be it. I'm a bit confused about taking the second backup though since we run the IndexOptimize prior to our full backups and the Log is still impacted. Is this true or not? As far as running it nightly, this was recommended by a DBA for this application and honestly I don't know if it's warranted or not, guess we'll have to look into that further..
    – cspell
    Oct 9, 2017 at 17:24
  • 2
    >>>I'm a bit confused about taking the second backup though since we run the IndexOptimize prior to our full backups and the Log is still impacted<<< That is because FULL BACKUP has NO IMPACT on a LOG BACKUP, log backups are independent of full backups, even if it is written as a comment and even as the answer. The next log backup will always start where the previuos log backup was stopped, so you can take even 10 full backups but your log backup still will grab ALL THE LOG RECORDS that were written to log since the last LOG BACKUP.
    – sepupic
    Oct 9, 2017 at 19:45
1

You can check how much the database has changed since the last backup using SQLskillsDIFForFULL.sql.

For log, you need to use the undocumented fn_dblog or documented DBCC LOGINFO or in sql 2017 and up using sys.dm_db_log_stats --> column log_since_last_log_backup_mb.

You might be having operations running overnight like rebuilding indexes or loading the data (as mentioned by @hot2use)

-4

It sounds like the system is configured so the first transaction log is a full log (back how far, system setting?) and the remainder are incremental.

1
  • This does not make sense. Do you have any links to show what you mean?
    – Tom V
    Oct 7, 2017 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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