SQL SERVER – Small Backup for Large Database

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.

SQL SERVER - Small Backup for Large Database smallbackup

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)

, , ,
Previous Post
SQL SERVER – Finding The Oldest Query Plan From Cache
Next Post
SQL SERVER – Writing Your First Subquery From a Single Table

Related Posts

3 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.

    Reply
  • 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”.

    Reply
  • 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.

    Reply

Leave a Reply

Menu