Increase VM Disk and Consolidate SQL Database

Increase VM Disk and Consolidate SQL Database

I have a ProxMox VM with the 3 virtual hard drives named ide0, ide1, and ide2. Ide1 is a 166GB drive that I want to expand. The ProxMox ID for the VM is 102, and I want to add 334GB to the drive, so I run qm as follows on the ProxMox host (via ssh):

qm resize 102 ide1 +366G

After processing, the ProxMox Web UI should show the new size:

I guess because of the age of the OS, I had to reboot the VM before the OS would see the extra space.  But upon reboot, I could see the extra 366GB in Disk Management. Now I had to expand the existing volume to include the new 366GB. Because the VM was on Windows 2003, I had to use DiskPart to do this (Disk Management works in later OSes).

Run Diskpart.exe in a CMD window, and type list volume to identify the volume you want to expand. Then type select volume x (where x is the volume ### you want to expand), and finally type extend to expand the volume. Type exit to leave DispPart:

Since the database was setup on the E: drive, and set to use a secondary datafile on the F: drive, I had to go into the database properties and in the Files category set the main database file to unrestricted growth.

Once that was done, the following SQL commands will consolidate the database back to a single file on the E: drive (the database in this example is SB79 and its secondary file was named sb79_data2).

USE SB79
GO
DBCC SHRINKFILE( sb79_data2, EMPTYFILE )
GO
ALTER DATABASE [sb79] REMOVE FILE [sb79_data2]
GO

The database is now back to a single file on the E: drive and I can remove the F: drive now.