Solved

calculating size of table space

Posted on 2007-11-23
7
260 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

685 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