Sql-server – alter column varchar to nvarchar – will file group on full drive have issues

sql server

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?

Best Answer

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.

Related Solutions

Sql-server – After moving a DB to a new disk, the log file isn’t dumping data to the DB

However, two days later, the .mdf file is the same size as pre-transfer (I took it offline and only the timestamp changed).

There was enough free space in your data file for any new data that was written. When that spare/free space is consumed the file will grow, not before. You can confirm the size and free space in your data file with:

SELECT 
    [File Name] = name
    , [Physical Name] = physical_name
    , [Total Size in MB] = size/128.0
    , [Available Space In MB] = size/128.0
        - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 
FROM 
    sys.database_files;

I checked the log, which is usually under 1GB, and found that it has now reached 30GB.

Your database is in full recovery mode and you aren't backing up the log. For this and all manner of transaction log growth explanations, see @MikeWalsh's canonical answer on the topic at Why Does the Transaction Log Keep Growing or Run Out of Space?

Sql-server – SQL Server 2008R2 minimum RAID/Disk config

For a 4 bay configuration with a high insert/update workload I would go with one of these:

1) Spindle only

One RAID1 for the log. Back this up with a a few hundred MB of write cache
One RAID1 for the data files and tempdb

Rationale: you will mostly be writing to the log. Latency of log is king for OLTP and it doesn't get much better than a RAID1 with a write cache. You can use SATA - which should give you at least 80MB/sec sequential writes at latency less than 1ms.

2) SSD plus spindle

One RAID1 for log with same configuration as the above.
One RAID1 for data files and tempdb

Rationale: This config will favour higher tempdb load and would be good if you are skimping on DRAM (as this will cause checkpoint activity to go up and drive read I/O)

3) Pure SSD

Put them in a RAID5 if you are not worried about wearing them out.
If you are using consumer grade SSD - put them in a RAID10.

Rationale: RAID5 with 4 x SSD is fast enough for nearly all purposes.

Everything goes on same LUN

4) 8 bay, spindle only config

Put 2 disks in RAID1 for the log file.
Rest goes into a RAID10 or RAID5 (RAID10 if you are worried about tempdb)

Overall: always prefer a 2 spindle RAID1 with a write cache for logs. It's plenty for 99% of all cases (80MB/sec for SATA and up to 120MB/sec for SAS).