Why is the database size larger than sum of table sizes?

Question

The data base total data files sizes are 35GB. The following are the top 10 tables with sizes in KB's. But tables sizes are around 1GB but database size 35GB why?

Row count Size of table(KB)
-----------    -----------------
 873737        41384.00
  59071        14752.00
   6336        12128.00
 107267        11872.00
  47066         9832.00
  24560         8848.00
 227576         8352.00
  24906         5328.00
  45506         3880.00
   2823         2904.00
  31313         2656.00

Answer 1

0

There could be a number of reasons for the database to be taking the size it does on disk.

It could have been created that big. There is no reason not to make a database large enough to store all the data it will ever need to when it is created. This saves growing the files and introducing fragmentation over time.
There may have been a large transaction or that forced the log file(s) to grow.
There may have once been a lot of data in the database but it has since been removed in an archiving process.

To see where the space is you can use these queries when connected to the database you are interested in

DBCC SQLPERF(LOGSPACE) -- http://msdn.microsoft.com/en-us/library/ms189768.aspx

and

EXECUTE SP_SPACEUSED() -- http://msdn.microsoft.com/en-us/library/ms188776.aspx

read how they work and what information they provide at the links shown

answered Jul 03 '12 at 07:43 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

No Large trans are running, No open trans running, Log file is also 300MB only but data files only have 34GB space but tables are have that much space only.... Why it happend need to get the details?

Jul 03 '12 at 09:12 AM narendba

I wouldnt expect there to still be a transaction running. Good job checking the logfile size. So, at some point the data file was set to a certain size or grew to a certain size.

Can you see which tables are taking the space?

Jul 03 '12 at 09:17 AM Fatherjack ♦♦

 name           file size in mb        space used in mb    free space in mb      max size
 ***********     *****************          *******************    ********************      ****************** 
  DataFile             17140.88                  17140.50                 0.38                 18048.00   
    LogFile              236.50                      12.50                  224.00                2000.00         
    DataFile1           6140.00                     6139.50                 0.50                   7048.00     
     DataFile2           6150.00                     6149.56                 0.44                   7048.00       
     DataFile3           6394.00                     6303.44                90.56                  14246.00

Find the above database individual file details .... Full Backup file size 35GB around... Largest table size is 40MB aroun....

Jul 03 '12 at 10:33 AM narendba

So it would appear that the database was created at this size or it has been resized to its current size

Jul 03 '12 at 11:09 AM Fatherjack ♦♦

how are you getting the database and table sizes? If you are getting this from SSMS Object Explorer Details then you need to add the Index Space Used (KB) column too. Indexes may well be consuming a large amount of DataFile space

Jul 03 '12 at 11:48 AM Fatherjack ♦♦

show 2 more replies

(comments are locked)

Answer 2

0

I would suggest that you are storing a lot of BLOB data and your method of calculating row sizes doesn't take that into account.

Does the dB store .doc, .xml, .mp3, .xls, etc.?

answered Jul 04 '12 at 12:21 PM

Blackhawk-17
10.5k ● 23 ● 29 ● 34

Great Just I had a talk with apps owner they told me users are uploated attachments into one table.... But how to fine BLOB data and size details?

Jul 04 '12 at 01:32 PM narendba

SELECT DATALENGTH(fieldname) FROM tablename

Jul 04 '12 at 01:37 PM Blackhawk-17

(comments are locked)

Why is the database size larger than sum of table sizes?

Follow this question

Related Questions