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!
Sql-server – Why is the First Transaction Log backup of the day huge
backupsql serversql server 2014
Related Solutions
As you can see in this table, there is no conflict between Full backup and Log backup. They can occur at same time, and, when log backup completes, log will not be cleaned until full backup (or diff backup) completes.
Not all administrative tasks are allowed to run concurrently. In the table below, a black circle indicates two operations that cannot run in a database at the same time.
References:
If the logs are full, then yes you will probably be looking at needing the same space to backup them up.
If you're running SQL Server 2008 Enterprise then you may find that enabling backup compression will save you plenty of space, unfortunately the only way to really find out how much is to run the backup (do you have a dev environment where you could restore a copy to?).
The other option if you don't need the ability to restore to a point in Time previous to when you do this, would be to:
put the database into Simple Recovery mode Checkpoint put database back into Full Recovery Take Full backup Start taking log backups.
By putting the database into Simple Recovery you break the log chain, so when you move back to Full Recovery there will be no log as such for the first log backup to take. Don't forget to take the Full Backup so that SQL Server can start a new log chain.
As mentioned this will mean you'd lose the ability to recover the database to 15:25 21/10/2012 for example. But you'd still be able to restore to a full or differential backup.
Added 03/06/2013
Sorry, I completely forgot this option as well:
Do you have any remote storage options? You can just backup the transaction logs to a UNC path. This may be a bit slower depending on your network but it'll mean you'll have a full transaction backup covering the previous period and can then start taking smaller regular backups.
Related Question
- Sql-server – Two Different Sources Taking Transaction Log Backups
- Sql-server – Transaction log backup file larger than expected
- Sql-server – SQL server 2014 Transaction Log Backup fails
- Sql-server – Transaction log backups cleaning
- Sql-server – The same First and the Last LSN
- Sql-server – Is an MDF “Last Modified Date” affected by transaction log backup
- Sql-server – Why is the backup sizes of AlwaysON replicas different
- Sql-server – After a full backup, how does system know which transactions to restore from the transaction log backup
Best Answer
That depends on what is happening in the night.
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.