Memory space


I have taken space SQL Server 2008 database  from a vendor. I have created 7 tables, 8 stored procedures and 3 tables. The number of lines of code in UDF and stored procedure is not more than 15-20 lines. And the average  number of records in the table is 15.

I have asked my vendor regarding the space usage and he said 3MB. I am confused how so much space is used. What is the memory space tables, proc and functions take.

Karan Gupta
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
each user table will use at least 8KB, each system table also (sysobjects, sysindexes etc ...)
so, 3MB looks like reasonable, though I did not check out, I think a db can be less large.
however, 3MB looks like a really small db, what is the issue about that (small) size?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tapan PattanaikSenior EngineerCommented:
Hi Karan,

please check this.

create table #test (
name varchar(255),
numrows int,
reservedk varchar(50),
datak varchar(50),
index_size varchar(50),
unused varchar(50)
insert into #test
exec sp_msforeachtable "sp_spaceused '?'"

select * from #test

for more details:
Please check this link:
be very clear about what you and the vendor are talking about when you say space....

there is a lot of overhead (comparatively) in the system objects required to support a database...

are you including log space?

how is the database organised ?
  (The data on the database itself can be organised as a series of physical files...
    the default is 1 file for the data (*.mdf) and 1 file for a log (*.ldf)
   however it is often prudent to use more than 1 physical file for the databases data, and even perhaps to
   separate the system tables from the user data tables...)

Is the vendor "charging/Assessing"  you on a portion of the storage costs of other system databases in the instance...?

how did you load the data into the database , via files or via the SSMS interface? (are the physical data files included...)

finally how are your tables structured ...
   what datatypes are you using for the columns  fixed or variable length
   what physical factors did you define the tables with (freespace...)

Databases are defined as an intial size , and with growth factors...

so the database physical file sizes may consume 3MB of disk space,  but you may only have "filled" <100KB worth of data...
and still have most of the other 2.9MB of space available to use...

Adding 10 paisa...

The model database is used as the template for all databases created on an instance of SQL Server. When you create database then we copy lot of system objects. You can use following query to fetch those objects related information:
select object_name(p.object_id) as object_name , p.index_id , p.rows , au.total_pages

from sys.allocation_units au left outer join sys.partitions p on (au.container_id = p.hobt_id)

where au.type in ( 1 , 3 )


select object_name(p.object_id) as object_name , p.index_id , p.rows , au.total_pages

from sys.allocation_units au left outer join sys.partitions p on (au.container_id = p.partition_id)

where au.type in ( 2 )

You can also execute following statement to check how much unused space you have under that user database and you can shrink the database size

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.