Our database server has 2 drives.
The first drive is full, so we had to add a new file on the 2nd drive so new data went to that drive.
This all worked great, and the mdf on full drive stopped growing and all new data goes to drive 2.
I now need to alter a column to go from varchar to nvarchar.
I know a lot of this data exists on the first drive in the MDF file that has 9mb remaining.
What will happen if I try to alter the column as the pages are on the full drive?
Is this a valid concern or will the file group stuff work it out and put the bigger column on the new drive?
So 1 filegroup with 2 files, one on each drive ?
In this case the writes will be distributed according to the space available on each file –
see the MS reference :- https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15
So the SQL Server engine should sort it out – however you might not get the best performance for this you would probably want a new filegroup with a number of equally sized files – but life gets complicated quickly and for most databases it is not worth the extra planning / management for the small percentage performance improvements available.
Only precaution I would take is to set the maximum size of the file on the nearly full disk to be equal to its current size, as you wouldn’t want it to attempt an autogrow and for that to fail.