Solved

calculating size of table space

Posted on 2007-11-23
7
257 Views
Last Modified: 2012-05-05
Reference:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22963775.html

when i execute sp_spaceused: i get
19999 rows, 3352 reserved, 2592 kb data, 624kb index size and 136 kb unused

i work out my table rows to be about 10400 rows
the following data types here are:

int  = 4
datetime = 8
tinyint = 1
nvarchar(15) = 30
int = 4
int = 4
int = 4
int = 4
money = 8
money = 8
money = 8
money =8
datetime = 8

overhead = 7
row offset = 2

total rowsize = 108

would anybody explain what is wrong with this calculation. I'm not too sure if it is the right method?
What is the amount of space that needs to be reserved for the  table and what else would i require to know to calculate the table size.
0
Comment
Question by:salesprod
  • 3
  • 3
7 Comments
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 300 total points
ID: 20341640
See http://technet.microsoft.com/en-us/library/ms187445.aspx

It also significantly depends on whether the table has clustered index. If it does, the rows are organized quite differently.
0
 

Author Comment

by:salesprod
ID: 20341643
how can i know if it's clustered index?
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 300 total points
ID: 20341684
you can open the table for design, right-click, select indexes, and see if any is clustered.

or use

sp_helpindex 'mytable'
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:salesprod
ID: 20341699
there are no indexes.
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 300 total points
ID: 20341777
Then use the article. It's anyways the best information available.

But when you say "space reserved for the table", what do you mean? usually you don't have to reserve anything. Unless it's some really huge table and you need to calculate how many hard drives you need to buy, for example...
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 200 total points
ID: 20343115
This is the way I use, I find it much simpler than to do calculations...

Create a new filegroup with a single file.  Move the table (if there is a clustered index, simply move it to the filegroup in index menu) as well as all indexes that belong to that table to the new filegroup.  Once this is done, you just need to take a look at the data file size.  That's it.  ;)
0
 

Author Closing Comment

by:salesprod
ID: 31410738
question solved
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now