Sql-server – Impact on database while increasing database initial size

disk-spacesql server

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.

Best Answer

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"