November 16, 2009 at 11:59 am
One of the databases I inherited is 35GB on the production server. (all numbers rounded off for simplicity)
Enterprise Manager -> Right click on database -> Properties -> Size = 35GB.
Looking at the 2 files on disk: Data file = 22GB, Log file = 13GB, total file size on disk = 35GB.
So I did a backup, and the backup file is about 18GB.
I wanted to transfer this to a test server, so I compressed it with 7Zip.
THE COMPRESSED FILE IS 369MB!!!
That can't be right, can it??
Just to be sure, I restored this database on the test machine. Sure enough, it blew back up to 35GB, all the data is there, all the tables, stored procedures, maintenance plans, everything.
I then right-clicked on the database and did Shrink Database, over and over again choosing every possible option, trying to make it as small as possible. I also ran DBCC SHRINKDATABASE a few times with different options.
The database is now 27GB, and when I compress it it drops to only 517MB (which is weird!).
But anyway, SQL uses 27-35GB to hold data that can easily be compressed to 369-517MB.
That's just ridiculous!
Isn't it?
November 16, 2009 at 12:22 pm
Why do you think that is rediculous? It just means that the data has many redundant bit patterns that allow it to be highly compressed.
November 16, 2009 at 1:32 pm
Lynn Pettis (11/16/2009)
Why do you think that is rediculous? It just means that the data has many redundant bit patterns that allow it to be highly compressed.
Lynn - I didn't even think of that -- thanx! You may very well be correct. This is an inventory management system, and I'm not familiar with the data, so there could be a huge number of repeats, or whatever. I guess I may want to dig in and look at the actual data, do lots of GROUP BYs or whatever and see if there are a relatively few number of items that repeat a lot.
Thanx!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply