Recently, I got a very interesting question from my client of the Comprehensive Database Performance Health Check. As many of you know that I primarily focus on the SQL Server Performance issue. If your server is running slow, I would like to help it to run faster. My client recently noticed that their server was running slow and they investigated the slowness in the system. After investigating their server, they found out that they have a full backup running for one of the largest servers and at the same time they are running a log backup on the same server. Now their question was is that fine or will that will have a negative impact on their backup?
Let me answer the main question – It is totally fine to have Log Backup and Full Backup running at the same time. While they both are running for the safety of the database and they are related to each other with the timeline, it is totally fine to have them running overlapping and they do not impact the integrity of the database or the backup.
Running Log Backup
The full backup does not truncate or remove data from the transaction log. Your transaction log file operates kind of independent from the full backup file. When you take full backup and transaction backup both at the same time, the transaction log backups are cleaned only after the full backup completes. One more thing, if you only run the full backup, your transactions log will remain intact and will not clean up. Only transaction log backup can clean the transaction file.
Let me know if you like what we discussed today. I am always looking forward to your question and often I created videos on them as well. Here are my recent 25 SQL in Sixty Seconds Video.
Reference: Pinal Dave (https://blog.sqlauthority.com)
I was looking for explanation on why transaction log cannot be cleaned (truncated) until full backup finishes? What architectural SQL server principles prevent doing this? As we sometime run to an issue with transaction log drive getting full if full backup is running for prolonged time.
hello sir , i want to know how to set for passord for backup in maintaince sql server 2014 and also mdf file