2

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?

Update 1

Here's a screen shot of the current FILES for this DB.

enter image description here

2

While it will work, it's not necessary to move tables to a new filegroup.

If you simply add a file to the database's existing filegroup(s) SQL Server will start using the new file(s).

SQL Server uses a "proportional fill algorithm" when a filegroup has multiple files. So if you add a new file (perhaps on a different volume) to a filegroup, SQL Server will write new data to that filegroup until it's the same percentage full as the current file.

|improve this answer|||||
  • Thanks @davidbrownemicrosoft for your answer! Really appreciated your time. So, I'm trying to understand what you said. Firstly, I've updated my Original Post with a pic to include the CURRENT files setup for this DB. So I have 3 files - 1 per FILEGROUP. So are you suggesting that I add a 2nd file to the PRIMARY filegroup and .. the "proportional fill" will now 'know' of the other disk and start filling that instead? Secondly, Should I add a maxsize to BOTH files so it doesn't try to use more diskspace that the volume's max size? – Pure.Krome Jan 15 at 0:23
  • Also @davidbrwnemicrosft - how does backup/restores work, with respect to adding a new file to a filegroup? If my production server has a filegroup with 2x files (as suggested above in your answer) while my dev/staging sql server has a massive storage disk with 1 file .. it can restore ok? So it restores to filegroups and in this scenario, it's the same PRIMARY filegroup and Sql Server is smart enough to allocate data to which ever FILE is linked to the group (1x File or 2x Files, etc) using the Fill Algorithm? – Pure.Krome Jan 16 at 2:09
  • Restore always creates all the same files. But you can choose where the files go on the target server. IE you can always restore all the files to the same folder if you want. – David Browne - Microsoft Jan 16 at 2:11
  • So before we do a restore, we need to make sure the destination-sql-server has the same number of files with the same logical-names before the restore? Otherwise, how does it know where to default-place the files upon restore, if the files don't already exist? – Pure.Krome Jan 16 at 2:20
  • 1
    Thanks heaps David - I really really appreciated your time and help with this one. – Pure.Krome Jan 16 at 2:59
1

Your idea to move tables to a new filegroup should work. It will take those tables offline for the duration of the move, but then they will be available for queries again. You can potentially limit the amount of downtime by doing them strategically, one at a time.

Note if you were on Enterprise Edition, you might be able to utilize the ONLINE index operation version of this to keep the table available for queries. Since you're on Web Edition, that's not an option.

Here I'll create a database, with a 1GB table in it:

USE [master];
GO
CREATE DATABASE [257236];
GO
USE [257236];
GO

CREATE TABLE dbo.OneGigTable
(
    Id int IDENTITY(1,1) NOT NULL,
    [BigColumn] nvarchar(max) NOT NULL,

    CONSTRAINT PK_OneGigTable PRIMARY KEY (Id)
);
GO

INSERT INTO dbo.OneGigTable
SELECT TOP (16777216) 
    N'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2
CROSS JOIN master.dbo.spt_values v3;
GO

And you can see this data file (on the default, primary file group) is mostly full:

screenshot of disk usage report in SSMS

Next I'll add a new filegroup, and add a file to that group. Note that you'll need to set the FILENAME to a path on your new disk. You should also size it appropriately based on the amount of data you expect to move into it:

ALTER DATABASE [257236]
ADD FILEGROUP NewFileGroup;
GO

ALTER DATABASE [257236]
ADD FILE
(
    NAME = NewFile_1,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\NewFile_1.ndf',
    SIZE = 1024MB
)
TO FILEGROUP NewFileGroup;
GO

Now, running the "Disk Usage" report in SSMS, I can see that new file is mostly empty:

enter image description here

Now I'll rebuild the table onto the new filegroup:

CREATE UNIQUE CLUSTERED INDEX PK_OneGigTable
ON dbo.OneGigTable (Id)
WITH (DROP_EXISTING = ON)
ON NewFileGroup;

And you can see now that the primary filegroup is mostly empty, and all of the data has moved to the new filegroup:

screenshot of SSMS disk usage report

|improve this answer|||||
  • 1
    Hi @joshdarnell - thanks heaps for the answer. Again, I really appreciated it. Right now I'm going to try David Browne's answer above because it means I might be able to AVOID having a table locked for the 'copy-over'. That said, I really like the detail to your answer and just wanted to give you some kudos to that. Very very nice and helpful. I'll report back if the other suggestion, works. – Pure.Krome Jan 15 at 0:26
  • @Pure.Krome I really appreciate your kind words! I was glad to see David show up with a simpler solution, he's awesome. – Josh Darnell Jan 15 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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