Impact on database while increasing database initial size 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 &amp; 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.
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