Moving Raw Messages Table

Most of the data storage in iService databases tends to be concentrated in 3 LOB (Large Object) tables:

•FilesRawMessages

•FilesText

•Files

For example, it's not unusual to see table sizes such as this list of the largest tables in an iService tenant. In a tenant with a large number of incoming emails, we've seen these 3 LOB tables account for over 90% of a 163GB database: 

The largest, FilesRawMessages, contains data which is infrequently accessed.  It is used to store raw emails as they come into the tenant before Customer Email interaction types are generated from the raw email.  After this initial action, they are seldom, if ever, accessed again.  However, they do remain in the database for archival purposes and can be retrieved when an iService agent clicks on a Customer Email's "Original Message.eml" link:

The combination of this table's large size and infrequent access  make it a good candidate for moving to a lower tier (cheaper/slower) storage solution than the rest of the tenant.  This guide demonstrates how to move the data in the FilesRawMessages on a live tenant to a filegroup located on lower tier storage.

•This guide was tested with iService 7.6 and will work with versions 6.0 until 7.6.  While it may work, it has not been tested with later versions.

•This will work with SQL Server 2008r2 to SQL Server 2016  It may work with later versions, but has not been tested.  In all versions of SQL Server, this must be performed with Raw Messages unavailable (offline) because although 2012 and higher support online LOB index rebuilds, they cannot be performed on ntext() columns. 

•In highly-available systems where 1. mail processing cannot be offline for the time it takes to rebuild and 2. Old interactions are purged on a regular schedule, we suggest simply doing a standard table partition at the current FilesRawMessages row ID and skip performing the index rebuilds which move the old data.  Over time, new Raw Messages will enter into the new partition’s filegroup, while old data is purged out of the old partition.

•SQL Server Enterprise Edition is required, which allows table partitioning.

•This guide will focus on moving the FilesRawMessages table, but the same process applies for any of the LOB tables.

•This process may take a very long time and can be very IO intensive. It's recommended to be done during low-usage periods.

•Your database's log file may grow since there are probably many more transactions occurring during this operation than normal.  You may want to note the initial size, and shrink the log back to that size when you're finished with the process.

•Thanks to SQL Mag for coming up with the original process this is based on.

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.

Tip

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.