Solved

calculating size of table space

Posted on 2007-11-23
7
262 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 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