Recently, I had a very interesting experience with one of my customer while working with them on Comprehensive Database Performance Health Check. The issue was related to small backup for the large database and it was so much interesting I decided to share with all of you.
Real World Customer Story
While working together with their health check of the server, a client asked me that they believe there is some issue going on with one of their databases. The size of their database was in over 900 GB but the backup of the entire database was less than 300 MB.
The customers were really worried about the smaller size of the backup and were suspicious that backup did not contain all the data. They asked me to look into this and I had a very interesting finding for this scenario.
Empty Big Log File
After carefully look into their database, I realize that they had a big log file which was pretty much empty and due to same reasons, they were under impression that their database is very big but when the backup was happening it did not contain the empty part of the log file and that was the reason for the smaller backup file.
Recreate the Scenario
Let us re-create the scenario.
CREATE DATABASE [BigLog] ON PRIMARY ( NAME = N'BigLog', FILENAME = N'D:\BigLog.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'BigLog_log', FILENAME = N'D:\BigLog_log.ldf' , SIZE = 8192000KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB ) GO
If you go and check the size of the file in the explorer you will notice that log file is of 8 GB big. However, as we have just created the database and have not populated them, the entire database is technically empty.
Now let us take backup of the entire database using the following script.
BACKUP DATABASE [BigLog] TO DISK = N'D:\BigLog.bak' WITH STATS = 10 GO
Now let us go and check the size of the backup file.
You will notice that even though the size of the database is overall very big when SQL Server takes a backup, it remove the empty space and take backup of the data only. This is indeed a very good feature otherwise, the size of the backup will be unnecessarily big.
The Best Practices
As per the best practices, it is always a good idea to Shrink your log file before taking the full backup as it will remove the unnecessary empty space in the log backup. This will be very helpful when you try to restore the database. When you try to restore your database from the tiny backup it will eventually create your data and log file to the original size when the database backup.
This means if you do not shrink your log file before you take the full backup, when you restore the database, it will create the log file with the empty space and it will essentially waste your important drive space.
Your Turn
Do let me know if you find this story interesting or not. I have many such stories I will be happy to share based on your feedback. Additionally, if you have any such interesting stories with your customers or at a workplace, do share with me in the comment and I will publish it with due credit to you.
Reference: Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
According to who is ‘shrink your log before doing a full backup’ a best practice ? I might need to do this if restoring to a non-prod server that doesn’t have the space available but I certainly wouldn’t do it as a routine practice as there could be a valid reason why that log file got ‘big’ in the first place. And if you shrink it, it will block updates while it grows which will be a bad thing if it has to grow to a very large size.
Bad advice, IMO.
With respect to the following statement in the article… (I agree with Tom Meyer’s comment) …
“As per the best practices, it is always a good idea to Shrink your log file before taking the full backup as it will remove the unnecessary empty space in the log backup.”
Be real careful when you say something is a “Best Practice” because people (usually neophytes but not necessarily) will think that something is a “Best Practice” when it’s actually not. Constant shrinking of the Log File before a backup will require the log file to grow back to the size the system actually needs and growing the log file is very expensive time wise. Even the use of “Instant File Initialization” won’t help much because the log file has to be formatted with VLFs. Worse yet, if you can’t shrink the log file to the exact same size each time, you may be fragmenting the heck out of the log file and creating a bunch of unnecessary VLFs or removing some necessary ones, not to mention that the number of VLFs can be very important to performance and such routine shrinks may make it impossible to “stay with the plan”.
Sir, thank you for your precious inputs on many topics.
There is one scenario that I have encountered, where-in, we had a huge DB(100GB mdf file and 190GB ldf file). The backup size of the DB has come to around 3.7GB.
Later, we shrank the size of ldf file to 1GB. Even after this, the size of our DB back up remained same. Is such a behavior accepted. I was expecting that the backup size file would become small after reducing the size of log file by such huge amount.
Any inputs on this will be much appreciated. Thank you.
There are scenarios where this is good advice. I encountered this precise scenario recently as part of a data transformation project. A client had taken a copy of their ERP production database and then removed all but a small fraction of the data, which I was to work with. They gave me a SQL backup file of that copy database that was 1.7 GB in size, but when I restored it onto my SQL environment the database file was 1.3TB, and the log file 26GB. Over 99.5% of that consisted of empty pages within the database for which I had to provision disk space in order to perform the restore. Had they shrunk the database before taking the backup, it would have saved hours in provisioning additional disk and performing the restore.
@yamini, what you described is exactly what Pinal explained, the backup only holds the transactions needed in case of a restore, your log file was huge but only had a portion of it that is needed in case of restore, so the size of your backups would be the same, unless you purge data in your database and shrink the database file, the backup will be the same size.
You’re considered to a bit of a “world leader” in the area of SQL and so a lot of people (especially newbies) are going to take your word for things as you’ve written them. Two of us have warned against you saying the following because it is, in fact, not only NOT a best practice, but is actually a worst practice.
“As per the best practices, it is always a good idea to Shrink your log file before taking the full backup as it will remove the unnecessary empty space in the log backup. ”
Please change that because it is, in fact, a worst practice to “always” shrink your log file before taking a backup if it’s just going to grow again.
Hi Pinal,
Many thanks for your feedback but in my case MDF file is around 61 GB what can be done in this case?