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

disk-spacefilegroupssql server 2014

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…

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.

Best Answer

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.

Related Solutions

Sql-server – Using schema + Filegroups or entirely separate database(same instance) to partition some big tables

Your problem is apparently all about storage space.

Your SSD array is not large enough to hold all your history on SSD.

Creating another filegroup is a good way to separate the use of storage space. That way you control what goes onto the historic data disk array. (When creating objects on a filegroup other than default, you must be sure to specify the filegroup you are using of course. )

Depending on how massive the historic data is, you may not need to split the data by quarters, etc.

For example, you were running well on the SSDs until space became a problem. So, remembering that space is the problem, you could ask yourself questions like:

Are my indexes well-designed for this workload?
Do my queries require scanning all of the historic data? (Apparently not, according to your comments above.)
Is the overhead of maintaining quarterly databases going to buy you significant query improvements? (Especially if you will need data from more than one database.)

Making things simpler and well-designed should be a first goal. But, if that is not sufficient then you should add layers as they are needed. Keeping performance statistics will help you measure the benefits.

There are plenty of web sites with help. Here is one set of queries by Glenn Berry: http://www.sqlskills.com/blogs/glenn/category/dmv-queries/

Sql-server – SQL Server – What backup/restore strategy should I use to accomplish the following

Given the following info:

Our support engineers need to grab database backups from clients.

and:

The most "valuable" data is "the configuration" of our system. It is contained in about 50 tables...

and:

I'm concerned about temporarily changing the recovery model for this purpose [of allowing the "Data" filegroup to become ReadOnly so that it can be omitted from the backup]

I would suggest a slightly different approach: use SqlPackage.exe (part of SQL Server Data Tools / SSDT) to either "extract" or "export" the desired tables and data into a single archive file which can then be retrieved and either "published" or "imported". SSDT is free, so there should be no licensing issue related to putting the SqlPackage.exe executable (and any dependent DLLs, etc) on the client systems.

You just need to make sure that SqlPackage.exe (and any dependent DLLs, etc) are on each client system. You can initiate SqlPackage.exe from xp_cmdshell. You can even create a .CMD script that executes SqlPackage.exe with the appropriate command-line parameters (there are several -- or you can place most options into an XML-based "publish profile" file and specify that on the command-line). You would then transfer the archive file (.dacpac or .bacpac) locally, just like you are transfering the backup file now. If you are FTPing to your local server from theirs, you can include the FTP commands in the .CMD script and then if you place the EXEC xp_cmdshell 'ExportAndTransferData.cmd'; statement into a stored procedure, you would only need to execute that stored procedure :-).

Looking at the various options, and knowing that you want a subset of the tables, I am thinking that you should first attempt an "Extract" (i.e. /Action:Extract. You don't want all tables, so then specify: /p:ExtractAllTableData=false. Then, for each table that you do want, specify: /p:TableData=schema.table (you will have around 50 of these options specified given that you want around 50 tables). Finally, it might be a good idea to verify the extraction by specifying: /p:VerifyExtraction=true.

Once the .dacpac file is retrieved locally, you would "Publish" it (i.e. /Action:Publish. For this action you just need a template database with all of the schema (tables, PKs, FKs, indexes, views, etc) and stored procedures / functions already there. This operation will just put the data into the proper tables.

Example CMD script (ExportAndTransfer.cmd)

@ECHO OFF

SET SQLPATH="C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin"

%SQLPATH%\SqlPackage.exe ^
    /Action:Extract ^
    /OverwriteFiles:True ^
    /SourceDatabaseName:ClientDatabaseName ^
    /SourceServerName:(local) ^
    /TargetFile:%TEMP%\ConfigurationTables.dacpac ^
    /p:IgnoreExtendedProperties=True ^
    /p:ExtractAllTableData=False ^
    /p:TableData="dbo.Table1" ^
    /p:TableData="dbo.Table2" ^
    /p:VerifyExtraction=true

ftp -s:path\to\FtpScript.txt my_server_hostname

Example Stored Procedure to call CMD script

CREATE PROCEDURE dbo.RunExtract
AS
SET NOCOUNT ON;

EXEC xp_cmdshell 'C:\path\to\ExportAndTransfer.cmd';

GO

All you need after those two is the ftp script.

UPDATE

While the "Extract" operation is generally more forgiving than the "Export" operation due to "Export" not liking various types of objects, in this particular case, the "Export" operation worked for the O.P. as it does not require grabbing all tables that are related via Foreign Keys. The "Extract" operation requires that, if a table is specified that has any FKs to other tables or if any other tables FK to it, all tables linked in any way to the desired table must be included. I found this MSDN forum thread discussing the reason for this requirement:

A dacpac can be published to any database -- empty or non-empty. And when table data is published, if the target table isn't empty, all of the data is dropped and replaced. But if the target table isn't empty and it has incoming FK dependencies, then there could possibly be broken FK references. Requiring that all tables be a closed set resolves this problem, because any incoming references will be erased as well.

There are fewer options to specify for "Export", so the command-line would look more like the following:

%SQLPATH%\SqlPackage.exe ^
    /Action:Export ^
    /OverwriteFiles:True ^
    /SourceDatabaseName:ClientDatabaseName ^
    /SourceServerName:(local) ^
    /TargetFile:%TEMP%\ConfigurationTables.bacpac ^
    /p:TableData="dbo.Table1" ^
    /p:TableData="dbo.Table2"