[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Full Backup size bigger than .mdf file

Posted on 2011-10-18
Medium Priority
Last Modified: 2012-08-14
Hi All, I have a database with .mdf file size under 2 GB and .ldf is right now 37GB for some reason.

When I took a full backup on the database, I surprised to see  the backup file to be 39GB when the .mdf file is only 2gb.

I though full backup will create a checkpoint and will flush out all the commited transactions in the .ldf to .mdf so that all the commited trasaction on teh .mdf file will be backed up and backup file size will reflect the size of .mdf file size.

but it proves wrong as the backup i had is the sum of .mdf and .ldf.

is that true, will the full backup size be the sum of size of bth .mdf and .ldf?
and why do backup include the un commited transactions in the .ldf files?

Question by:msdba
  • 2
  • 2
LVL 60

Accepted Solution

Kevin Cross earned 1500 total points
ID: 36990337
It sounds like you are not doing any transaction log backups which is why it is constantly growing. In addition, remember when transactions are committed, that does not mean the log file is shrunk. So if you have a large transaction that grows the TLog to 37GB then releases everything after, you will will be 37GB just 99% free. :)

What is your current RECOVERY MODEL?

Author Comment

ID: 37082868
Thanks for the comment.
It is Full recovery model.

but I guess full backup is about backing up the data in .mdf file, right?
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1500 total points
ID: 37086786
Yes, in Full Recovery model, the expectation is that you are doing a FULL BACKUP and then frequent T-LOG BACKUPS. To restore point-in-time, you are restoring the .BAK and then the .TRN files up to the point in time needed.

Author Closing Comment

ID: 37201432

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question