http://www.sqlservercentral.com/blogs/sqlandme/2011/10/19/sql-server-_1320_-moving-table-with-lob-data-to-different-file-group/

Printed 2014/09/01 06:26AM

SQL Server – Moving table with LOB data to different file group

By Vishal.Gajjar, 2011/10/19

Last week, i posted about moving tables to different file groups by recreating indexes. However, this method does not *work* when there is LOB data involved.

When we recreate indexes on a table containing LOB data, the LOB data pages are not moved to destination file group. This can be demonstrated with below example:

USE [master]

GO

 

/* Create a database with 2 file groups */

CREATE DATABASE [TestDB]

ON PRIMARY

(     NAME = N'TestDB',

      FILENAME = N'C:\Database\Data\TestDB.mdf' ),

FILEGROUP [SecondaryFG]

(     NAME = N'TestDB_2',

      FILENAME = N'C:\Database\Data\TestDB_2.ndf' )

LOG ON

(     NAME = N'TestDB_log',

      FILENAME = N'C:\Database\Log\TestDB_log.ldf' )

GO

 

/* Create a table on [PRIMARY] file group */

CREATE TABLE T2

(     [ID] INT IDENTITY(1,1),

      [Name] VARCHAR(MAX),

      [Status] SMALLINT

)

GO

 

/* store LOB data out of row */

EXEC sp_tableoption 'dbo.T2', 'large value types out of row', 1

GO

 

CREATE UNIQUE CLUSTERED INDEX [IX_CL_T2_ID] ON [dbo].[T2]

(     [ID] ASC

) GO

 

CREATE NONCLUSTERED INDEX [IX_NCL_T2_Status] ON [dbo].[T2]

(     [Status] ASC

) GO

 

INSERT INTO T2([Name], [Status])

VALUES ('LOB data', 1)

GO

 

SELECT      OBJ.name, OBJ.type, FG.name

FROM        sys.indexes IDX

INNER JOIN  sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

INNER JOIN  sys.objects OBJ ON IDX.object_id = OBJ.object_id

WHERE       OBJ.type = 'U'

GO

 

DBCC IND('TestDB', 'T2', 1)

GO

 

SELECT      DF.file_id, F.name

FROM        sys.filegroups F

INNER JOIN  sys.database_files DF ON DF.data_space_id = F.data_space_id

GO

Result Set:

name   type   name

T2     U      PRIMARY

T2     U      PRIMARY

 

PageFID PagePID     IAMFID IAMPID      ObjectID    …  iam_chain_type

1       158         NULL   NULL        2105058535  …  In-row data

1       157         1      158         2105058535  …  In-row data

1       156         NULL   NULL        2105058535  …  LOB data

1       155         1      156         2105058535  …  LOB data

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

file_id     name

1           PRIMARY

3           SecondaryFG

From the result set we can see that:

1. Both indexes are on [PRIMARY] file group.

2. All allocated pages are from file with file_id = 1

3. File with file_id = 1 is in [PRIMARY] file group.

Now let’s try to move the table to [SecondaryFG] by rebuilding indexes:

USE [TestDB]

GO

 

/* Re-create indexes on [SecondaryFG] */

CREATE UNIQUE CLUSTERED INDEX [IX_CL_T2_ID] ON [dbo].[T2]

(

      [ID] ASC

) WITH (DROP_EXISTING = ON)

ON [SecondaryFG]

GO

 

CREATE NONCLUSTERED INDEX [IX_NCL_T2_Status] ON [dbo].[T2]

(

      [Status] ASC

) WITH (DROP_EXISTING = ON)

ON [SecondaryFG]

GO

 

/* Indexes have moved to [SecondaryFG] */

SELECT      OBJ.name, OBJ.type, FG.name

FROM        sys.indexes IDX

INNER JOIN  sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

INNER JOIN  sys.objects OBJ ON IDX.object_id = OBJ.object_id

WHERE       OBJ.type = 'U'

GO

 

/* But not LOB pages,

PageFID = FileID of file from which the page is allocated */

DBCC IND('TestDB', 'T2', 1)

GO

 

/* File ID 1 is from [PRIMARY] file group

File ID 3 is from [SecondaryFG] file group */

SELECT      DF.file_id, F.name

FROM        sys.filegroups F

INNER JOIN  sys.database_files DF ON DF.data_space_id = F.data_space_id

GO

Result Set:

name   type   name

T2     U      SecondaryFG

T2     U      SecondaryFG

 

PageFID PagePID     IAMFID IAMPID      ObjectID    … iam_chain_type

3       9           NULL   NULL        2105058535  … In-row data

3       8           3      9           2105058535  … In-row data

1       156         NULL   NULL        2105058535  … LOB data

1       155         1      156         2105058535  … LOB data

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

file_id     name

1           PRIMARY

3           SecondaryFG

From this result set we can see that:

1. Rebuilding indexes have moved them to [SecondaryFG] file group.

2. In-row data pages are now allocated from File with file_id = 3

3. LOB data pages have not moved, and are still in file with file_id = 1

4. File with file_id = 1 is in [PRIMARY] file group, and File with file_id = 3 is in
[SecondaryFG] file group.

So how do we move the LOB data pages? You need to drop and recreate the table if you need to move LOB data. The general steps to follow are:

1. Create a new table with same structure on destination file group i.e. [SecondaryFG]

2. Import rows from old table

3. Delete old table

4. Rename new table

For example:

USE [TestDB]

GO

 

/* Create a new table on new file group */

CREATE TABLE Tmp_T2

(

      [ID]        INT    IDENTITY(1,1),

      [Name]      VARCHAR(MAX),

      [Status]    SMALLINT

) ON [SecondaryFG]

GO

 

EXEC sp_tableoption 'dbo.Tmp_T2', 'large value types out of row', 1

GO

 

CREATE UNIQUE CLUSTERED INDEX [IX_CL_Tmp_T2_ID] ON [dbo].[Tmp_T2]

(

      [ID] ASC

) ON [SecondaryFG]

GO

 

CREATE NONCLUSTERED INDEX [IX_NCL_Tmp_T2_Status] ON [dbo].[Tmp_T2]

(

      [Status] ASC

) ON [SecondaryFG]

GO

 

/* Import data from old table */

SET IDENTITY_INSERT Tmp_T2 ON

INSERT INTO Tmp_T2([ID], [Name], [Status])

SELECT * FROM T2

SET IDENTITY_INSERT Tmp_T2 OFF

GO

 

/* Delete old table */

DROP TABLE T2

 

/* Rename the temporary table */

EXEC sp_rename 'dbo.Tmp_T2', 'T2'

GO

 

/* Rename indexes, not necessary but useful when they have been used in hints. */

EXEC sp_rename    N'dbo.T2.IX_CL_Tmp_T2_ID',

                  N'IX_CL_T2_ID',

                  N'INDEX'

GO

 

EXEC sp_rename    N'dbo.T2.IX_NCL_Tmp_T2_Status',

                  N'IX_NCL_T2_Status',

                  N'INDEX'

GO

This script implements the steps described above:

1. It creates a new table/indexes dbo.Tmp_T2 on [SecondaryFG] with the same structure
as dbo.T2

2. Imports all rows from dbo.T2 into dbo.Tmp_T2

3. Deletes dbo.T2

4. Renames dbo.Tmp_T2 to dbo.T2. It also renames the indexes as required.

You can check the page allocations using the queries we used earlier:

USE [TestDB]

GO

 

/* Indexes are on [SecondaryFG] .. OK. */

SELECT      OBJ.name, OBJ.type, FG.name

FROM        sys.indexes IDX

INNER JOIN  sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

INNER JOIN  sys.objects OBJ ON IDX.object_id = OBJ.object_id

WHERE       OBJ.type = 'U'

GO

 

/* LOB Pages are on File ID = 3 */

DBCC IND('TestDB', 'T2', 1)

GO

 

/* File ID 3 is in [SecondaryFG] .. OK. */

SELECT      DF.file_id, F.name

FROM        sys.filegroups F

INNER JOIN  sys.database_files DF ON DF.data_space_id = F.data_space_id

GO

Result Set:

name   type   name

T2     U      SecondaryFG

T2     U      SecondaryFG

 

PageFID PagePID     IAMFID IAMPID      ObjectID    …    iam_chain_type

3       11          NULL   NULL        2121058592  …    In-row data

3       10          3      11          2121058592  …    In-row data

3       9           NULL   NULL        2121058592  …    LOB data

3       8           3      9           2121058592  …    LOB data

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

file_id     name

1           PRIMARY

3           SecondaryFG

From this result we can see that all indexes and LOB data pages have moved to file with file_id = 3, which is in [SecondaryFG].

Hope This Helps! Cheers!

Reference : Vishal (http://SqlAndMe.com)


Filed under: SQLServer
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.