CODEWITHSUNDEEP
sql-serverdatabasesize
Jeff
Jeff

Reputation: 433

what should my initial database size be

I'm moving a database from MySQL to SQLServer. When creating the database, what should I set the initial size to? I know the size of the imported database will be about 130MB but will grow. Should 130MB be the initial size or should I just take the default of 2MB?

Upvotes: 7

Views: 29457

Answers (5)

Junior Mayhe
Junior Mayhe

Reputation: 16411

USE MASTER;
GO
CREATE DATABASE StackOverflowDatabase
ON 
( NAME = 'StackOverflowDatabase',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\Your version\MSSQL\DATA\SO_db.mdf',
    SIZE = 200MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 50MB )
LOG ON
( NAME = 'StackOverflowDatabase_log',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\Your version\MSSQL\DATA\SO_db.ldf',
    SIZE = 20MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 10MB ) ;
GO

Upvotes: 2

Hossein A
Hossein A

Reputation: 1260

One thing you should find out is how fast the DB is growing.

If the growth rate is small (let's say 1 MB/week)

For the MDF,

Set initial size to 200 MB

Set auto-growth to 100 MB (which is 50% of the initial size)

For the LDF,

Set initial size to 20-50 MB (which is 10-25% of the initial size of the MDF file)

Set auto-growth to 10-25 MB (which is 50% of the initial size of the LDF file)

Avoid using % for auto-growth because it is unpredictable. For instance, if you specify percentage for your auto-growth and the DB size grows to 300MB than this means 30 MB. If your DB size grows to 500 MB this means 50 MB. On the other hand, if you use MB then you are assured that the DB will always grow the amount of MB you set.

Upvotes: 0

mattmc3
mattmc3

Reputation: 18335

200 MB with a 50 MB auto-grow is the right solution. Remember to use separate drives for tempdb, and if possible put your logs on different disks from your data too if you need better performance. Also, remember that your data may be 130 MB, but you need to think of your indexes too and how much space they'll consume. Also, even if you have a 200MB data file, your backups files will be much smaller, which is often where the real space concern is when talking about small DBs like this.

Upvotes: 0

Chris Diver
Chris Diver

Reputation: 19812

You should make it the correct size to fit your data, you will get a performance hit whenever the file needs to grow.

It depends how fast it would grow, I would say 150MB with 10% Autogrowth.

There is advice on the MSDN that is worth a read.

Upvotes: 5

Taylor Bird
Taylor Bird

Reputation: 8007

Set it to at least your current size, probably with a decent buffer for immediate growth during the migration. Depending on growth rate I would do something like:

Initial: 150MB (or 200MB if size isnt an issue)

Autogrowth: yes

Autogrowth Size: anywhere from 5MB to 25MB (depending on your growth expectations)

Upvotes: 3

Related Questions