Hello,
I am new with MS SQL and there is one thing i don’t understand.
Our Database file (mdf) has a size around 70 gb. I can do copy only backup and the bak file is around 10-12 gb. Where did the data go?
Regards,
Lennart
I “use the default server settings”. You know how to figure out the default backup compression settings?
It didn’t go anywhere. You have to remember that the raw database is filled with thousands of empty columns. When the backup gets compressed it ignores all that “whitespace” and you end up with what is more a true size of your database. Our MDF is about 48GB our backup is about 2.25GB it’s pretty standard 
2 Likes
Our database backup is about the same size as the actual database file. They are within a few GB of each other. If you’re running database maintenance on a regular schedule you shouldn’t have the extra space I don’t think.
to add what others have mentioned. don’t forget indexes add size to the db as well.
1 Like
Thanks for the answers.
I am not worried anymore.
Your business will drive the size but I would recommend run one of the standard tasks in SSMS to review your top 100 tables. I have a script that runs monthly to spot tables that seem to be growing.
Just spent a few weeks with support purging the ABTwork and Local100 tables. After I was done we got 1/3 of our space back!
Regards,
Graeme
1 Like
Do you know what the purpose of this table is?
Found my ticket from 2 years ago and the analyst only said that “the local100 table is a deeper issue than I originally thought. It is used by multiple programs in ERP10.”
2 Likes
There is a standard fix program available:
I found that by running some other fixes that altered thousands of records, it was storing an XML record of the before and after in this table and consuming vast amounts of GBs. This clears it out, and haven’t seen any side effects.