Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I have a database that is size about 104GB.

When I calculate the total table sizes in the database it's only about 24GB. This includes Data+index+unused spaces.

Why the DB size is too large?

share|improve this question
Do you have LOB storage? XML columns? I am assuming this is SQL Server...? – Yuck Dec 22 '11 at 15:01
3  
Have you tried compacting the database? Are you taking into account padding, metadata and other fancy stuff internal to the storage engine? – Cicada Dec 22 '11 at 15:07
Are you talking about the total size of your database?, I mean, data (.mdf file) and log (.ldf file)? – Lamak Dec 22 '11 at 15:08
(Assuming MS SQL Server) The database size includes the transaction log (.ldf file(s)). You need to look at specific file sizes. – squillman Dec 22 '11 at 15:08
How do you calculate unused spaces? Databases usually trade space for speed, and allocate huge amounts of disk space ahead of time, to avoid allocation at transaction time. Space freed by delete may be reused or not due to speed reasons; try coalescing some of the database objects to reclaim it. – 9000 Dec 22 '11 at 15:09
show 5 more comments

migrated from stackoverflow.com Dec 22 '11 at 18:10

3 Answers

up vote 2 down vote

Depending on the RDBMS being used, it is possible that the large file size as compared to summed-up table sizes is caused by any or more than one of the following:

  • Last database cleanup (in terms of table deletion, record deletion, etc) did not affect any disk reclamation by the OS/RDBMS

  • Last database cleanup does not normally affect disk space reclamation by OS/RDBMS

  • Indexes/constraints are being stored in other files. You won't see these when checking just table sizes.

  • No shrinking has been performed, though that may also possibly end up as first and/or second in this list

  • A possible miscalculation of table size retrieval (I just need to add this because it happens to some) and so the numbers don't tally

  • And maybe a few others.. Hopefully the stackers can add

I'm not saying these are definite items that impact your situation. I just mean that these may occur, and not just in your situation..

  • One other thing. It can also depend on the database settings for new disk allocation sizes. Say for example one allocation for a certain DB is 1GB. Once the usage exceeds that, another 1GB is added, regardless of whether or not you consumed all of it -- at the moment more allocation is required, it gets set.
share|improve this answer
Thank you. One other think I am noticing is that, my db's initial size is 101890MB. I am not how did it happen? – pavelcc Dec 22 '11 at 15:37
Is this a production database? Do you have enough resources to back this up and restore it as a copy/non-production db? Then you can try to check the allocation size; I'm trying to remember if you can alter this by SQL.. Then you can also try to shrink the database. You can check your DB settings and search for an applicable shrink command: support.microsoft.com/kb/907511 --- AS MUCH AS POSSIBLE perform these tests on a non-production copy of the database. Try to restore your most recent backup, or even back the DB up right now, restore elsewhere, then use that instance to test.. – Nonym Dec 22 '11 at 15:44
This is in production server. I don't have enough space to backup the db – pavelcc Dec 22 '11 at 16:37
up vote 2 down vote

In one of your comments you said the initial size of the database is shown in SSMS as 101890MB. That's why the database is so large, it was created at that size.

As to how/why it was created at 104GB, check the model database on that server. Good odds someone has fiddled with the defaults in model and your database has inherited those when you created it.

share|improve this answer
up vote 0 down vote

If you are talking about os file size versus computed bytes for the records you store, then this will depend on your database type. If you take Oracle as an example, you create tablespaces as containers for your tables and indexes. These tablespaces pre-allocate space on disk via data files. Then you start allocating blocks out of the tablespace for different tables until your tablespace is full. But you may not have even created a table yet but the os will have a file for the empty tablespace. Then you also have tablespaces for system tables, rollback information, journaling information and the like. So there is a certain amount of overhead before you even get into record counts.

If you did a dump of your data tables, the files generated by the dump may be more closely related to your calculated data sizes.

share|improve this answer
Thank you for your comments. I am on SQL server 2005 OS-2003 – pavelcc Dec 22 '11 at 15:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.