SQL Server – Transaction Log Backup Impact on Application Performance

backupsql serversql server 2014transaction-log

We have a database around terabytes of data. We are taking FULL backup every week, differential backup daily and transaction log backup after every hour. Sometimes this transaction log backup takes some time to complete (around 10-15 minutes). In this period we find that application(s) performance affects a lot(normally very slow) that use the same database.

Does transaction log backup affect the performance of the application?
If yes, then how to make speedup the process of taking transaction log backup?

Best Answer

Does transaction log backup affect the performance of the application?

The transaction log backup reads from the transaction log file and writes to the backup location. The application writes to the transaction log file when it changes the database. If the log file or the log backup location and the database files or tempdb files are on the same disk (or even perhaps on the same SAN controller), then the log backup could impact access to those files too.

Capturing a snapshot of the wait stats during the application slowness should provide more details on what impact the backup is having.

Taking the backups more frequently will spread out the impact. But you also may need more storage resources (more or faster disks) for this database. With large databases you need to provide enough resources for both operating and maintaining the database, and maintenance tasks (like log backups, index maintenance, schema modification) can require significant additional resources.

Related Solutions

Sql-server – Is it OK to use WITH INIT for the Transaction Log Backup to avoid having it grow too large

No, do not use WITH INIT for transaction log backups to the same file. If you do that, you may as well not take them in between full/diff backups.

Typically you will backup the log to a unique file each time. Each one will have some form of a timestamp in the name and have a .trn extension rather than .bak. This is mostly for management purposes - it makes it really easy to identify when a log backup was taken, when it is safe to delete, whether it should be part of your point-in-time recovery operation, etc.

SQL Server – Transaction Log Backups

would there by any TR Log Backups for that time period?

If there is no activity at all, there would be not transactions. But there is no harm in taking that backup as a part of your backup schedule (which is every 15 mins).

It will look like -

/*Processed 0 pages for database 'logbackup_test', file 'logbackup_test_log' on file 1.
100 percent processed.
BACKUP LOG successfully processed 0 pages in 0.072 seconds (0.000 MB/sec).
*/

what would happen if I happen to restore those transaction log ?

You will be able to restore them in sequence or you can possibly skip it as well- if there is NO gap in the Log Sequence Numbers of the log backups..

Best Answer

Related Solutions

Sql-server – Is it OK to use WITH INIT for the Transaction Log Backup to avoid having it grow too large

SQL Server – Transaction Log Backups

Related Question