• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

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
4 Solutions
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?
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: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20884783.html
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now