x

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

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
more ▼

asked Jul 03, 2012 at 05:36 AM in Default

narendba gravatar image

narendba
570 28 39 46

I am getting the staticstic by using top 10 largest tables scirpt and database file sizes script.....
Jul 04, 2012 at 11:31 AM narendba
SELECT SUM(DATALENGTH(image)) FROM tablename
Jul 04, 2012 at 03:27 PM Blackhawk-17
@narendba - you can get details of the DATALENGTH function and its output by placing your cursor in the function name and pressing F1. All the details will be there
Jul 05, 2012 at 09:33 AM Fatherjack ♦♦

Try

SELECT SUM(CONVERT(NUMERIC(38,0),DATALENGTH(filetype))) FROM tablename
Jul 05, 2012 at 11:12 AM Usman Butt
It returns number of bytes. I guess you have not followed Fatherjack's advice to see the details related to DATALENGTH function.
Jul 09, 2012 at 12:28 PM Usman Butt
show all comments (comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Jul 03, 2012 at 07:43 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

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, 2012 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, 2012 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, 2012 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, 2012 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, 2012 at 11:48 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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.?
more ▼

answered Jul 04, 2012 at 12:21 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

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, 2012 at 01:32 PM narendba
SELECT DATALENGTH(fieldname) FROM tablename
Jul 04, 2012 at 01:37 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1949
x40
x3

asked: Jul 03, 2012 at 05:36 AM

Seen: 2889 times

Last Updated: Jul 09, 2012 at 12:31 PM