Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

calculating size of table space

Posted on 2007-11-23
7
Medium Priority
?
264 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 600 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 600 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
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.

 

Author Comment

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

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 600 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 400 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

715 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