Solved

calculating size of table space

Posted on 2007-11-23
7
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

623 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