I'm quite familiar with SQL-Server as a developer but not so much in the DBA role.
Currently I'm working on a rather large DB (~7TB, living in 7 files, all equal in size and all within one single file group). The DB is running out of disc space.
The customer is going to increase the space available by 2 TB.
To state some things first:
- The system is a fail over cluster
- It is – why ever – no option to add a second file group.
- I know that using a second file group might offer great benefits… (not wanted by the customer. It's an old system and nobody dares to touch it more than necessary…)
The options I'm thinking of:
-
I could use
ALTER DATABASE
withMODIFY FILE
to increase the size of the existing files (~1TB each at the moment). -
I could use
ADD FILE
to add one or two additional files similiar to the existing ones.
The questions
-
Is there anything special to keep in mind with fail over clusters'?
-
Is there any reason why to prefere one of the options?
remark: The goal is as little impact as possible.
Best Answer
No
Yes, especially depending on your IO subsystem. SQL Server uses two algorithms when deciding which file to use for allocating new extent. Round robin and proportional fill. My recommendation will be to use your first option.
I could use ALTER DATABASE with MODIFY FILE to increase the size of the existing files (~1TB each at the moment).
Paul Randal explains this in details with example, Investigating the proportional fill algorithm.