HUGE Massive Ridiculous SQL 2000 file sizes! Something MUST be wrong! Right?

  • 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?

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