In our environment the network storage is low on space. At the same time I would like to make sure that we take transaction log backups every 15 minutes instead of current every 6 hours. My question is will changing the log backup interval from 6 hours to every 15 minutes consume more disk space?
Transaction Log Backups – Disk Space Usage Comparison
backupdisk-spaceNetworksql servertransaction-log
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:
Shrinking your transaction log should not be part of your routine. A transaction log backup clears the log file automatically; shrinking it afterward just causes it to have to grow again afterward. Read this for more information.
Yes, you should be able to perform point-in-time restores using your latest full backup and any subsequent transaction log backups. Anything more recent than the latest transaction log backup will require that the server still be operational enough to take a tail-log backup
The critical point is to test it. Backups are not reliable until you know from experience that you can restore the data successfully. This article and the links at the bottom of that article have some good information about SQL backup strategies.
Related Question
- SQL Server – Transaction Log Backups
- Sql-server – Run Transaction Log backups only during Work hours
- SQL Server – How to Clean Transaction Log Backups
- Sql-server – Reclaim Transaction Log space of SQL Server
- Sql-server – Txn Log backups – does it backup empty space
- SQL Server – Maintenance Plan for Creating Transaction Log Backups
Best Answer
No, but it may make your transaction log smaller - so your SQL Server will use less space.
To keep things simple, say you generate 1MB of transaction log activity every minute. After 15 minutes, you've generated 15MB of log activity - but that also means that your transaction log will need to be at least 15MB large (assuming that you're in full recovery model.)
If you back up that log file right away, and you don't have open transactions (or any of a host of other things that will cause SQL Server to keep the log file in use), you'll be able to clear out portions of it, keeping your log file small.
However, if you wait 6 hours, that's 360MB of logged activity - your transaction log file (LDF) would be larger.
I'm using small, easy numbers here - you'd want to change them for your own environment to see the impact on your own transaction log space.