• Repost - originally posted 05-08-2012

Standardise database sizes and automation

The default setting for the initial sizing of data and log files (and their autogrowth) is not adequate.

I like to make sure that we set standard sizes for our data and log files - plus standard sizes for their autogrowth.

You could categorise your database sizes and set the standards per this size.

For example: Category and Sizing Very Small Data file 25MB with 5MB growth Log file 10MB with 5MB growth Small Data file 50MB with 10MB growth Log file 25MB with 5MB growth Medium Data file 500MB with 100MB growth Log file 100MB with 25MB growth Medium/Large Data file 2500MB with 500MB growth Log file 500MB with 100MB growth Large Data file 5000MB with 500MB growth Log file 500MB with 100MB growth Very Large Data file 10000MB with 2000MB growth Log file 1000MB with 500MB growth

Once you have done this, you could then setup a template for each category, creating a database of the required size. This can then be used in automating your database creation by just passing the database name and the category you require, the database should get created automatically. With this automated database creation you could also set any other database options that you have as a standard (e.g. recovery model)

2 views0 comments

Recent Posts

See All

Migrating SSIS packages to AWS

We are currently migrating a large amount of SSIS packages (SQL Server 2008R2) to SQL Server 2016 at AWS. It has been a learning exercise to get these packages running. What we have learnt (so far) is

Review missing index findings before you implement

There are a number of queries widely available that will look at the DMVs and tell you what indexes you may be missing. This is a great help with performance tuning your SQL Server. But be cautious of

Autoclose and autoshrink - set them to off

By running a simple check of your database settings (use helpdb or query sys.databases) you may find databases that are set to autoclose and/or autoshrink. If so, you should change these settings as p