SQL server – Pre size a database

06 April,2011 by Jack Vamvas

A disadvantage of some modelling simulation applications , which use database servers ,is the undetermined database size at the point of CREATE DATABASE. Typically , a user will issue a CREATE DATABASE statement , and the default sizing values are applied. In SQL Server , the model database will act as the template for the sizing. The DBA can influence this sizing in two ways : 1)They can change the model configurations 2)They can resize the database after it’s created 


In most situations , a DBA would consult with the user and on the basis of projected workload pre size the database.

The main benefits of pre sizing are:

a)Pre allocating disk space – disk usage is projected

b)When a database grows during a workload – at the point where the database grows – a performance hit is experienced – this will vary according to the current size of the database.

For example, if a database is currently 100 GB – and there is a request to grow the database by 10% then resources will be redirected to growing the database, to 110 GB .


Best practise in that situation is to change the 10% growth to a hard number , such as 500MB. Therefore regardless of the database size it will only expand in increments of 500 MB , not 10% of the current size . For example if the database is currently 100 GB – then 10 % is 10 GB. You get the idea.

 The disadvantages are:

a)A process would need to be in place to pre size the database ,  a consultation with a user and DBA,

b)The user would need to forecast the database size

 In general there are  3 options

a)Don’t change anything

b)Create a procedure to pre size the database

c)Change the default setting of the database growth rate from 10% to a definite size . This could be in conjunction with b).

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL server – Pre size a database

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer