In our example, the original iService filegroup is located on the E: drive, and we'll be moving FilesRawMessages to the lower-tier F: drive.  Our tenant database is around 14.5 GB:

 

 

 

A query for the FilesRawMessages clustered index size tells us that about 4.5GB of space is allocated to it:

 

 

USE [iservice.1to1service]
GO

SELECT i.[name] AS IndexName
        ,Sum(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
        AND s.[index_id] = i.[index_id]
WHERE i.NAME = 'PK_FilesRawMessages'
GROUP BY i.[name]
ORDER BY i.[name]
GO

 

 

 

First, create a new filegroup in the tenant database. We'll call it LOBdata.

 

USE [master]
GO

ALTER DATABASE [iservice.1to1service] ADD FILEGROUP [LOBdata]
GO

 

 

Add a new file located on the F: drive, inside the new LOBdata filegroup. Earlier we noted that FilesRawMessages was 4.5GB, so we're going to make the initial size 5120000KB to hold it all.

 

USE [master]
GO

ALTER DATABASE [iservice.1to1service] ADD FILE (
        NAME = N'iservice.1to1service_LOBdata'
        ,FILENAME = N'F:\DATA\iservice.1to1service_lobdata.ndf'
        ,SIZE = 5120000 KB
        ,FILEGROWTH = 204800 KB
        ) TO FILEGROUP [LOBdata]
GO

 

 

Next, we're going to partition the FilesRawMessages table, with the first partition residing in the LOBdata filegroup and actually containing all the rows in the table. The second partition will contain no rows and will reside in the original Primary filegroup.  This will allow us to later re-index the table, moving the data to the LOBdata filegroup while keeping the database online.

 

 We need to find what the highest row ID currently is in the FilesRawMessages table so that we can create a RIGHT  partition range much higher than it, so that any new rows created during the move also are within range:

 

 

Our highest ID is 140362, so a right range of 200000 should be enough. Create the partition function:

 

USE [iservice.1to1service]
GO

CREATE PARTITION FUNCTION rawmessagemove (INT) AS RANGE RIGHT
FOR
VALUES (200000);

 

Then apply the partition to the FilesRawMessages table, with partition #1 mapped to LOBdata, and partition #2 mapped to Primary:

USE [iservice.1to1service]
GO

CREATE PARTITION SCHEME rawmessagemove AS PARTITION rawmessagemove TO (
        lobdata
        ,[primary]
        );

 

Next, we'll perform the actual move of the data, by dropping and recreating the indexes of FilesRawMessages onto the new filegroup.  Let's get a list of the indexes we'll be working with:

 

USE [iservice.1to1service]
GO

SELECT dbschemas.[name] AS 'Schema'
        ,dbtables.[name] AS 'Table'
        ,dbindexes.[name] AS 'Index'
        ,indexstats.alloc_unit_type_desc
        ,indexstats.avg_fragmentation_in_percent
        ,indexstats.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
        AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
        AND dbtables.[name] = 'FilesRawMessages'
ORDER BY indexstats.avg_fragmentation_in_percent DESC

 

 

 

There are 3 indexes we'll need to move - the row data clustered index, the LOB data clustered index, and the unique unclustered index.  As an aside, you might want to take note of the fragmentation shown, and compare it to the fragmentation after the move is complete.

 

First we'll drop and recreate the row data clustered index on the new partition (this may take a few minutes, and mail won't be processed during this time if doing it in OFFLINE mode):

 

USE [iservice.1to1service]
GO

CREATE UNIQUE CLUSTERED INDEX PK_FilesRawMessages ON dbo.FilesRawMessages ([ID] ASC)
        WITH (
                        DROP_EXISTING = ON
                        ,ONLINE = OFF
                        ) ON rawmessagemove(ID)

 

Next we'll drop and recreate the lob data clustered index, on the new filegroup. This will move all the LOB data (this again may take a few minutes, and mail won't be processed during this time if doing it in OFFLINE mode):

 

USE [iservice.1to1service]
GO

CREATE UNIQUE CLUSTERED INDEX PK_FilesRawMessages ON dbo.FilesRawMessages ([ID] ASC)
        WITH (
                        DROP_EXISTING = ON
                        ,ONLINE = OFF
                        ) ON [lobdata]

 

 

Finally, we'll do the same for the unclustered 'unique' index.  Note that the name of the index will be different on your tenant. This should be quick compared to the other 2, and can be done ONLINE.

 

USE [iservice.1to1service]
GO

CREATE UNIQUE INDEX UQ__FilesRaw__43B15FA02EF4C8F4 ON dbo.FilesRawMessages ([hash] ASC)
        WITH (
                        DROP_EXISTING = ON
                        ,ONLINE = ON
                        ) ON [lobdata]

 

 

That's it! Your FilesRawMessages LOB have been moved to the other storage device.  Now that your LOB data has been moved, depending on your future expected growth patterns you may or may not want to shrink the database to free up the newly unallocated space.

 

Note that this is completely optional, and there are some very negative consequences for doing so, one of which is fragmenting your freshly created indexes. 

 

In this example, we'll do a database shrink simply to demonstrate that the FilesRawMessages table was moved to the new storage. 

1.Our original file in the primary filegroup on E: has been reduced from 14.5GB to around 10GB without FilesRawMessages.

2.The new file in the LOBdata filegroup on F: holds the 4.5GB of FilesRawMessages data.