x

Size of the database

How to calculate the size of the database knowing the datatypes of the fields in table and the expected number of rows?
more ▼

asked Oct 18 '10 at 03:20 AM in Default

aRookieBIdev gravatar image

aRookieBIdev
2.3k 42 54 58

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

To estimate the size of a database, estimate the size of each table individually and then add the values obtained. The size of a table depends on whether the table has indexes and, if they do, what type of indexes. It involves various calculations

For tables it is the sum of size of clustered index + the sum of the size of all the non clustered index

for heap it is page size i.e, 8192 * Number of pages that required to store all the rows

To know more, see [Capacity Planning][1], article from Microsoft

[1]: http://msdn.microsoft.com/en-us/library/ms187445.aspx
more ▼

answered Oct 18 '10 at 03:37 AM

Cyborg gravatar image

Cyborg
10.6k 36 39 45

I just tested Matt Whitfield's product named Atlantis Data Space Analyser and if this free tool is not the best thing since the sliced bread then I don't know what is. It graphically shows allocated, in-row, LOB, index etc spaces used by existing objects in the database. Since it is easy enough to write a script to mock the objects and data, the tool will let you see the whole picture before making a decision about storage requirements, and these picture will show exact numbers not just the estimates.
Oct 18 '10 at 04:34 PM Oleg
@Oleg - Thanks - that means a lot to me coming from you!
Oct 19 '10 at 12:50 AM Matt Whitfield ♦♦
thank you.Its very useful.
Oct 19 '10 at 02:30 AM aRookieBIdev
(comments are locked)
10|1200 characters needed characters left

Database management systems must constantly face the trade-off of "speed vs. space," and generally speaking they tilt in favor of speed.

Also, if the database is anticipated to be large enough that per-estimation of space requirements is likely to be a factor, many other issues can pop into reasonable consideration. You probably need to have a RAID array. You probably should distribute parts of the database to different drives and volumes. Once again you are paying, with space and drives, for ... speed, redundancy, and so on. Thus, the more "important" an advance space-estimate is perceived to be, the less likely it is to be accurate.

And if you want to know the future growth rate of your database you should try Lepide SQL Storage Manager which having capability to forecast your database future disk space growth rate and manage the capacity of database via performing many operation like defragmentation, Partition, rebuild & reorganize the indexes etc

http://www.lepide.com/sql-storage-manager/

more ▼

answered Mar 22 '13 at 12:59 PM

indexer gravatar image

indexer
26

doesn't answer the question.
Mar 22 '13 at 01:28 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x107
x8

asked: Oct 18 '10 at 03:20 AM

Seen: 819 times

Last Updated: Mar 22 '13 at 01:28 PM