15

Is there a tool/application to calculate space requirement for a database in SQL Server? I know sp_spaceused can be used, but it calculates the actual space being taken up at that moment. What I was looking for is a tool that connects to the database and the user can interactively provide input like average number of rows and get the space requirement. This can be used for planning for future.

  1. The tool should be able to connect to a SQL Server instance/database,
  2. Get data size for one row for each table
  3. User should be able to put estimated row counts for all the tables
  4. The tool should be able to estimate the size requirement

With some more input like transactions per day etc, we can estimate the size of the log etc.

Update:

I have started working on a kind of tool to meet this goal. I shall like everybody to pour in their ideas. Once it takes some shape, I am planning to put it in a open-source project site like codeplex.com

EJoshuaS - Stand with Ukraine
  • 11,977
  • 56
  • 49
  • 78
Kangkan
  • 15,267
  • 10
  • 70
  • 113
  • 2
    Good question! I'm not aware of any; we've created an internal system for tracking this over time (monthly size and record count collection/storage process, by database, table and client ID) – Tao Jun 07 '11 at 12:12
  • Please keep pouring in new ideas. If there is no such tool, we can think of creating one. – Kangkan Jun 07 '11 at 12:44
  • 1
    Last time I looked I didn't find one so ended rolling my own code to calculate average row sizes for the heap/CL IX and the associated NC indexes (using the official calculations) and then pumping this into excel where I could then apply values for a specific customer (such as how many products they sold, how many locations etc) – Andrew Jun 07 '11 at 13:47
  • 1
    You would also need to look at things like transactions per day (for the logs), inserting / deleting (is there going to be fragmentation introduced?). I guess what I'm trying to say is that even if you manage to create or find a calculator take the results with a grain of salt. – Mike M. Jun 07 '11 at 16:03
  • Right. One need is just calculate the size requirement for each row in each of the table. Then we might need a way to input average no of rows in each of the tables. And then plan the growth factor. Last, size of the log as well. – Kangkan Jun 08 '11 at 07:19

2 Answers2

6

I created a (free) tool to do this - https://github.com/mattrandle/DBSizer

The application produces a spreadsheet by reading schema information from your db. You enter a number of rows per table into the spreadsheet. It tells you approximately how big your db will be.

jb455
  • 100
  • 1
  • 7
Matt Randle
  • 1,885
  • 2
  • 17
  • 22
6

Below reads provide good clarity on space estimation

Estimating the Size of an SQL Server Database - http://sqlserverdiaries.com/blog/index.php/2011/05/estimating-the-size-of-an-sql-server-database/

Cindy gross checklist was very good - http://blogs.msdn.com/b/cindygross/archive/2009/03/12/previous-blogs-on-sqlcommunity-com.aspx https://web.archive.org/web/20151026141916/http://blogs.msdn.com/b/cindygross/archive/2009/03/12/previous-blogs-on-sqlcommunity-com.aspx

How to estimate disk space needed for SQL Server - Not accessible in sqlcommunity.com.

One more link, SQL Server Sizing Resources - http://blogs.msdn.com/b/bartd/archive/2010/06/16/sql-server-sizing-resources.aspx https://web.archive.org/web/20190421182553/https://blogs.msdn.microsoft.com/bartd/2010/06/16/sql-server-sizing-resources/

TCDutchman
  • 41
  • 11
Siva
  • 2,791
  • 5
  • 29
  • 33