Database Size & Management - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Database Size & Management

Author

Topic

val33
Starting Member

5 Posts

Posted - 2007-03-01 : 12:01:41

I am not an SQL guru by any stretch but I am trying to understand the differance bewteen the size of the data contained in the tables and the physical size of the .MDF file.

Currently I have a website for a client with 27 megs of Physical data stored in the tables but a Data file size of 334 MB set a 3% autogrow. I have my Log capped at 5mb, but I can't seem to contain the Data file. My database just keeps growing and growing every 2-3 days with very little new data being inserted.

Why is there such an enourmous differance bewteen the size of data stored in the tables and the size of the data file and is there anything I can do to fix it?

Thank you for any help,

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-01 : 12:12:44

How are you determining that there is only 27MB of data?

val33
Starting Member

5 Posts

Posted - 2007-03-01 : 12:22:06

I am running a table query that adds up the data size of every table in my database. This script in fact.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762&SearchTerms=table,reserved,size

val33
Starting Member

5 Posts

Posted - 2007-03-01 : 13:23:40

Here are the details of my database
3/1/2007 341 MB (336 MB Data Size/5 MB Log Size)

Here is my table breakdown.

Table Name Rows Reserved Data Index Unused
Ads 117 656 KB 296 KB 16 KB 344 KB
aspnet_Applications 2 64 KB 8 KB 56 KB 0 KB
aspnet_Membership 82 216 KB 88 KB 32 KB 96 KB
aspnet_Paths 0 0 KB 0 KB 0 KB 0 KB
aspnet_PersonalizationAllUsers 0 0 KB 0 KB 0 KB 0 KB
aspnet_PersonalizationPerUser 0 0 KB 0 KB 0 KB 0 KB
aspnet_Profile 75 72 KB 56 KB 16 KB 0 KB
aspnet_Roles 2 32 KB 8 KB 24 KB 0 KB
aspnet_SchemaVersions 6 16 KB 8 KB 8 KB 0 KB
aspnet_Users 182 104 KB 24 KB 80 KB 0 KB
aspnet_UsersInRoles 76 32 KB 8 KB 24 KB 0 KB
aspnet_WebEvent_Events 0 0 KB 0 KB 0 KB 0 KB
body 34 24 KB 16 KB 8 KB 0 KB
Brakes 11 24 KB 16 KB 8 KB 0 KB
categories 3 16 KB 8 KB 8 KB 0 KB
ClassifiedCategories 3 16 KB 8 KB 8 KB 0 KB
Classifieds 1 32 KB 24 KB 8 KB 0 KB
engine 41 32 KB 24 KB 8 KB 0 KB
make 33 40 KB 32 KB 8 KB 0 KB
Members 75 48 KB 32 KB 16 KB 0 KB
News 5 56 KB 48 KB 8 KB 0 KB
Options 46 24 KB 16 KB 8 KB 0 KB
photos 239 25,680 KB 24,840 KB 16 KB 824 KB
SiteMap 35 136 KB 88 KB 16 KB 32 KB
Transmissions 15 24 KB 16 KB 8 KB 0 KB
Years 29 16 KB 8 KB 8 KB 0 KB
Totals 1,112 27,360 KB 25,672 KB 392 KB 1,296 KB

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-01 : 13:36:13

Those numbers can be very wrong after changes to your database. Run
DBCC UPDATEUSAGE(0)
in your database to correct the numbers. That may change your total. Also you only see the size of your tables and indexes with those stats. That should be the bulk of the database size, but run something like
DBCC SHOWFILESTATS
to see actually usage within the database files. It shows the number of extents in use (an extent is 64KB).

Have you tried shrinking the database?

val33
Starting Member

5 Posts

Posted - 2007-03-01 : 14:54:04

I appreciate your help.

I did what you said and got the following.

Total Extents 5376
Used Extents 5359
Which is approx 342 megs

I also got the same results as before in the size of my tables all of which add up to 27 megs. What could be stored in my .mdf file that is the difference bewteen 27 megs of table data and the 342 megs of disk space being used? I guess that is where I don't really understand what is going on.

val33
Starting Member

5 Posts

Posted - 2007-03-02 : 10:35:38

Anyone have any idea as to why the Size of my Data File is near 13 times larger than the size of the data stored in the tables?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources