Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
 Posted - 2005-12-02 : 11:48:47
|
| Guys, Is there any way to calculate the physical size of the table on the database assuming that it is not on a seperate filegroupany suggestions/input would helpThanks |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-12-02 : 12:24:06
|
| Look in the SS2005 BOL - I completely rewrote the topics on estimating table and index size. Look for 'estimating database size' in the index and you'll find drill-downs into heaps, clustered indexes and non-clustered indexes. They also apply to SS2000 (just leave out the parts that take account of new SS2005 features).If you don't have SS2005 let me know and I'll post these topics here.ThanksPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2005-12-02 : 16:45:07
|
| Paul, presumably, you mean this page:http://msdn2.microsoft.com/en-us/library/ms187445.aspxOf course, the complete SQL Server 2005 Books Online can be downloaded without SQL Server 2005 from here (116394 KB):http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-12-02 : 16:47:29
|
| Wouldn't sp_spaceused do the calculation for you? I didn't think he was talking about estimating it, but rather what is it right now.Tara Kizeraka tduggan |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
|
|
mschroeter
Starting Member
1 Post |
Posted - 2005-12-08 : 15:44:56
|
| I'm currently going through a sizing exercise, and I've run into a problem using these formulas for calculating table/index sizes (they essentially remain unchanged since SQL2000 from what I can tell).I'm ending up with a difference of 12 bytes per row using the formula versus what the xmaxlen column in sysindexes tells me. The formula always comes up 12 bytes short. Now, Kalen Delaney in Inside SQL 2000 says that there are 10 bytes of overhead per row, which the formula from what I can tell does not account for. It also leaves me still 2 bytes short per row.So can anyone clear this matter up and offer something that's consistent with what the database actually says? I still don't know for sure how to get from A to B to calculate on paper what a table size should be and have it be accurate. |
|
|
|
|
|
|
|
|
Physical Size of a table