0

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?

1 Answer 1

2

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://learn.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.

1
  • great thanks, this answers my question
    – Quade
    Jul 31, 2020 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.