SQL Server db size estimator

What is it ?

SQL Server database size estimator is an application that reads schema information from a SQL Server database and creates a spreadsheet that estimates the physical size of the database given a number of rows per table. The database size is calculated by summing the estimated size of each table and index in the database.

The example spreadsheet below was created by running the application against the AdventureWorks 2008 OLTP sample database.  The spreadsheet includes macros to calculate the size of the indexes so you will need to enable macros in Excel to get it to work properly.

Download sample spreadsheet for Adventure Works 2008 OLTP db

The application uses the method described here to calculate the size of a database.

The output is loosely based on the spreadsheet created by Reuben Sultana in this blog post - http://sqlserverdiaries.com/blog/index.php/2011/05/estimating-the-size-of-an-sql-server-database/

For columns with variable length datatypes, the application can calculate an average fill length from existing data.

I tested it against SQL 2005 and 2008 but it should work with 2008 R2 and 2012.

It handles all Sql Server datatypes, including custom, binary, Sql variant, and UDT’s

Includes clustered and non-clustered indexes in size calculation

Download SQL Server Size Estimator

Release Notes

16th March 2013 – added missing datatypes from SQL Server 2008 R2
21st August 2013 – added missing datatype ‘DATE’. Fixed bug with index page size calculation
12th September 2013 – fixed Sql Authentication

Troubleshooting

The application has a dependency on SQL Server Management Objects.

2008 SMO can be downloaded here - http://www.microsoft.com/en-us/download/details.aspx?id=3522.  See section Microsoft SQL Server 2008 Management Objects.

If you get the error ‘Programmatic access to Visual Basic Project is not trusted’

You need to enable the option ‘Trust access to the VBA project object model’ option in Excel.

On the Excel Options, select Trust Center on the left hand side and click the  ’Trust Center Settings…’  button.

On the Trust Center dialog, select Macro Settings from the left hand side and check the ‘Trust access to the VBA project object model’ checkbox.

As you read this far, you should follow me on twitter here.