MS SQL running out of Disk Space – can FileGroups be used to free up some space?


Problem

We have a Sql Server 2014 with a 1TB attached disk (on Azure) that’s running out of disk space. We have about 20GB’s left (maybe a few weeks of space). As such, we need to move some data off the CURRENT disk and onto a NEW disk.

Details

Server:

Microsoft SQL Server 2014 - 12.0.2548.0 (X64)      Jun  8 2015 11:08:03      Copyright (c) Microsoft Corporation     Web Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) 

MS-SQL 2014 is installed onto a classic Azure VM. This VM is in a classic VNET. The storage DISKS are also classic. As such, we couldn’t just expand the existing disk. MS-Support said that we need to update all of these, if we wish to leverage the modern Azure Storage to allow disk resizing, using the newer SSD disk, etc. TL;DR; we can’t take this offline for hours including all the other subsystems that communicate with the VM via IP address. Now, don’t turn this into a flame-fest .. this is what we’ve been given to work with and will need to fix all of this up later.

So right now, an idea what to try and leverage FILEGROUPS and to move one or more tables into a FILEGROUP and push this FILEGROUP onto another DISK which we have attached.

So the questions here are:

  • Is this a really really crazy and lame idea, first of all?
  • If it’s crap but OK, then will using FILEGROUPS actually move the data from the CURRENT disk onto the NEW disk (which frees up some disk space on the nearly full CURRENT DISK)?
  • If this is still possible, does moving these tables mean the data is locked/unavailable … which means were still back to our initial problem 🙁
  • What about logs? Moving this data means the logs just get a copy of this? (we are doing hourly and weekly backups, I believe).

It’s ok if the data is small, but here’s a quick look at some of our tables…

enter image description here

That first table is massive (with respect to the rest of the data). 750GB ish.

I was thinking of moving maybe the 2nd, 3rd of 4th line, in the result image. Remember how I said the infrastructure is all on OLD classic stuff? This means the HD’s are old and slow so copying data could take some time also.

As an example, I just tried to copy the .mdf‘s (this DB has 1 main mdf and 2 other small ones) from OLD over to NEW disks. that had a quick ETA of 24 hours.

Having the site offline for a few hours is totally acceptable. We can take stuff offline when our customers are asleep. but … 24 hours .. that hurts. The 24 hour idea was a simple test for:

  • Create new 2TB Disk (if possible)
  • turn off sql server.
  • copy mdf + log files to new disk. (24 hours or so)
  • point filegroups from old location to new location
  • start sql server again.

Now, we’re open to ideas and I know that stack exchange is not a site for ‘opinions’ so I’m trying to keep this on target with a suggested answer and to get feedback on it … but we’re open to other solutions to reduce the offline time.

So – can anyone help please?