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!
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.
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
You might be having operations running overnight like rebuilding indexes or loading the data (as mentioned by @hot2use)