Impact on database while increasing database initial size

Question

I have a database whose initial size is 900 GB for .mdf file and 13 GB for log file, I want to increase its initial size to + 100 GB , while doing that in the properties -> filegroup->initial size, do I require a SQL Server restart or downtime after applying the changes?

Also while the size is getting increased, will it hold lock on all the reads & writes that is being done on the database?

I'm doing this as I am planning to pre-allocate size to my database instead of relying upon autogrowth. (I have got general suggestions from this site where pre growing the size of the database is considered to be better than auto growth).

We don't have instant file initialization on. I am new to this dba work and I wasn't aware of the usefulness of this until now.

Answer 1

Do I require a SQL Server restart or downtime after applying the changes ?

No.

Also while the size is getting increased, will it hold lock on all the reads & writes that is being done on the database?

No.

SQL Server is an enterprise product and designed with stability and consistency in mind.

That said, unless you have Instant File Initialization on, your performance may suffer and potentially significantly. Depending how good your IO subsystem handles 100GB writes. I would suggest doing something of that significance (100 of 900GB) during off hours, simply to avoid load impact.

If you have Instant File Initialization off, turn it on. You can find instructions here.

Alternatively, you can just Google: "SQL Server enable Instant File Initialization"

Answer 2

I want to increase its initial size to + 100 GB , while doing that in the properties -> filegroup->initial size, do I require a SQL Server restart or downtime after applying the changes?

No. Increasing initial size of the database should be transparent and does not require a service restart.

If your database is highly active, suggest you to do this during less active time.

Below are my recommendations :

• Enable auto growth to be in MB as opposed to a % increase.

• Enable Instant File Initialization, this prevents the writing of zeroes prior to space allocation. Which means when auto growth or when you have to increase size of data files, it's extremely fast.

Refer to my answer for : Database defragmentation and autogrowth settings

• Sayantani Nath: We are planning to presize or pre allocate size to our Database (mdf file) by adding 100 GB and remove the autogrowth factor, will this be helpful ? Also, how much autogrowth should be enabled for log file?
• TomTom: Large production: Zero, preallocate all. Otherwise do NOT make it percent, makeit a specific step . But logfiles REALLY do NOT like auto alloc.
• pim: I second the set MB growth over %. I typically use values of 256mb for data files and 128mb for log files.