2 added link to sql skills article
source | link

Please take a moment to think why these allocations are happening. SQL Server's log is designed to re-use disk space once the information currently held there is no longer required. This suggests a few scenarios for your case.

Perhaps the log disk space is not being released for re-use. This is possible if the database is in full recovery mode and log backups are not taken. Without the log backup SQL Server will retain past transaction log (t-log) data inside the log files so it can recover from a failure. Once the log backups have been complete SQL Server "knows" that past log data is safely in your backup repository and it can release the corresponding disk space for re-use. If you are in full recovery and not taking log backups you should be. Make them frequent enough to match your RPO. Every 15 minutes is not unusual.

It may be the transaction log file is being shrunk. During an auto-growth event SQL Server requests disk space from the operating system (OS) and adds that space to the t-log. During a shrink event the reverse happens and SQL Server hands back to the OS some of the disk space it has reserved. If you are shrinking t-log files stop doing it. It is self-evident that the space is needed so should not be handed back to the OS.

You may genuinely have more data each day that requires additional t-log space to process. Therefore the t-log files have to expand to accommodate that data. Then your suggestion of expanding the files and increasing the auto-growth increment makes some sense. There is an alternative, however.

Rather than process all the data within a single transaction you could process it in batches. Each batch will be its own transaction and use some t-log disk. When a transaction is committed the t-log space becomes available for the next batch to use (when in simple recovery) or can be made available by taking a t-log backup (when in full recovery). There is a trade-off between development complexity in your 8am process and keeping the t-log size under control. That's for your management and users to agree.

There's a lot of information and further links here.

Please take a moment to think why these allocations are happening. SQL Server's log is designed to re-use disk space once the information currently held there is no longer required. This suggests a few scenarios for your case.

Perhaps the log disk space is not being released for re-use. This is possible if the database is in full recovery mode and log backups are not taken. Without the log backup SQL Server will retain past transaction log (t-log) data inside the log files so it can recover from a failure. Once the log backups have been complete SQL Server "knows" that past log data is safely in your backup repository and it can release the corresponding disk space for re-use. If you are in full recovery and not taking log backups you should be. Make them frequent enough to match your RPO. Every 15 minutes is not unusual.

It may be the transaction log file is being shrunk. During an auto-growth event SQL Server requests disk space from the operating system (OS) and adds that space to the t-log. During a shrink event the reverse happens and SQL Server hands back to the OS some of the disk space it has reserved. If you are shrinking t-log files stop doing it. It is self-evident that the space is needed so should not be handed back to the OS.

You may genuinely have more data each day that requires additional t-log space to process. Therefore the t-log files have to expand to accommodate that data. Then your suggestion of expanding the files and increasing the auto-growth increment makes some sense. There is an alternative, however.

Rather than process all the data within a single transaction you could process it in batches. Each batch will be its own transaction and use some t-log disk. When a transaction is committed the t-log space becomes available for the next batch to use (when in simple recovery) or can be made available by taking a t-log backup (when in full recovery). There is a trade-off between development complexity in your 8am process and keeping the t-log size under control. That's for your management and users to agree.

Please take a moment to think why these allocations are happening. SQL Server's log is designed to re-use disk space once the information currently held there is no longer required. This suggests a few scenarios for your case.

Perhaps the log disk space is not being released for re-use. This is possible if the database is in full recovery mode and log backups are not taken. Without the log backup SQL Server will retain past transaction log (t-log) data inside the log files so it can recover from a failure. Once the log backups have been complete SQL Server "knows" that past log data is safely in your backup repository and it can release the corresponding disk space for re-use. If you are in full recovery and not taking log backups you should be. Make them frequent enough to match your RPO. Every 15 minutes is not unusual.

It may be the transaction log file is being shrunk. During an auto-growth event SQL Server requests disk space from the operating system (OS) and adds that space to the t-log. During a shrink event the reverse happens and SQL Server hands back to the OS some of the disk space it has reserved. If you are shrinking t-log files stop doing it. It is self-evident that the space is needed so should not be handed back to the OS.

You may genuinely have more data each day that requires additional t-log space to process. Therefore the t-log files have to expand to accommodate that data. Then your suggestion of expanding the files and increasing the auto-growth increment makes some sense. There is an alternative, however.

Rather than process all the data within a single transaction you could process it in batches. Each batch will be its own transaction and use some t-log disk. When a transaction is committed the t-log space becomes available for the next batch to use (when in simple recovery) or can be made available by taking a t-log backup (when in full recovery). There is a trade-off between development complexity in your 8am process and keeping the t-log size under control. That's for your management and users to agree.

There's a lot of information and further links here.

1
source | link

Please take a moment to think why these allocations are happening. SQL Server's log is designed to re-use disk space once the information currently held there is no longer required. This suggests a few scenarios for your case.

Perhaps the log disk space is not being released for re-use. This is possible if the database is in full recovery mode and log backups are not taken. Without the log backup SQL Server will retain past transaction log (t-log) data inside the log files so it can recover from a failure. Once the log backups have been complete SQL Server "knows" that past log data is safely in your backup repository and it can release the corresponding disk space for re-use. If you are in full recovery and not taking log backups you should be. Make them frequent enough to match your RPO. Every 15 minutes is not unusual.

It may be the transaction log file is being shrunk. During an auto-growth event SQL Server requests disk space from the operating system (OS) and adds that space to the t-log. During a shrink event the reverse happens and SQL Server hands back to the OS some of the disk space it has reserved. If you are shrinking t-log files stop doing it. It is self-evident that the space is needed so should not be handed back to the OS.

You may genuinely have more data each day that requires additional t-log space to process. Therefore the t-log files have to expand to accommodate that data. Then your suggestion of expanding the files and increasing the auto-growth increment makes some sense. There is an alternative, however.

Rather than process all the data within a single transaction you could process it in batches. Each batch will be its own transaction and use some t-log disk. When a transaction is committed the t-log space becomes available for the next batch to use (when in simple recovery) or can be made available by taking a t-log backup (when in full recovery). There is a trade-off between development complexity in your 8am process and keeping the t-log size under control. That's for your management and users to agree.