主页 / dba / 问题 / 196818
Accepted
Martin Guth
Asked: 2018-02-02 05:21:33 +0800 CST

包含多个文件的文件组：移动内容时的奇怪行为

  • 772

我目前正在将数据库从 SQL Server 2008 (SP4) 迁移到 SQL Server 2017 (CU3)。一个显着的变化是所有文件组在迁移后都有两个数据文件。为完成此任务，我恢复了一个备份，添加了包含两个大小相同的文件以及相同的自动增长设置的新文件组，并使用以下语法传输数据：

CREATE UNIQUE CLUSTERED INDEX <PK of the table> ..... WITH (DROP_EXISTING = ON ,...) ON <new Filegroup>

不幸的是，我还必须移动一些 LOB，然后事情会变得有点复杂：

  • 添加分区方案和分区函数（基本上具有相同的目标）
  • 在新的分区方案上创建聚簇索引（WITH DROP_EXISTING=ON）
  • 在新文件组上创建聚簇索引 (WITH DROP_EXISTING=ON)
  • DROP 分区方案和函数

这种技术由 Kimberly Tripp在这里描述，可以追溯到 Brad Hoff。

感谢您的关注，现在回答我的问题：

通过重建这样的索引，文件组中需要多少可用空间？

我给你举个例子：

  • 我有一个大小为 220GB 的 LOB 表（根据 sys.allocation_units 中的总页数除以 128 除以 1024）。
  • 将我的新（空）文件组（仅托管该表）中的两个文件预置为 220/2 = 110 GB，每个文件增长 = 0。
  • 尝试使用上述技术传输表但收到错误消息

无法为数据库“abc”中的对象“xyz”分配空间。“xyz_pk”因为“def”文件组已满。通过删除不需要的文件、删除文件组中的对象、向文件组添加其他文件或为文件组中的现有文件设置自动增长来创建磁盘空间。

  • 将每个文件大小增加到 112 GB...再次尝试并收到相同的错误消息
  • 最后打开文件的自动增长并且该过程成功完成

但是，每个文件的大小为 220 GB，每个文件中还有 50% 的可用空间。

这是建议的诊断查询 表的文件大小 的输出文件大小加起来为 227,22 GB。

到目前为止，我不知道有任何其他补救措施DBCC SHRINKFILE可以消除可笑的大量可用空间。然而，这并不是我特别引以为豪的事情……它需要很长时间才能留下腐败等。

你能帮我理解为什么 SQL Server 分配了这么多可用空间，然后又愉快地按比例填充我的两个文件吗？

之后我会尝试准备一个演示……抱歉，我现在时间不够，也许你们中的一些专家已经知道这样做的原因。

在此先感谢您的帮助

马丁

sql-server filegroups

1 个回答

  1. Best Answer

    首先感谢你们帮助我跟上你们的评论。

    我现在已经完成了一个示例，并且更好地理解了发生了什么。

    将 LOB 数据（例如 VARCHAR(MAX)、XML 等）移动到另一个文件组时会出现问题。当您在另一个文件组上重建聚簇索引时，LOB 数据会保留在原来的位置（由TEXTIMAGE ONCREATE TABLE 语句中的命令设置）。

    移动 LOB 数据的一种经典方法是在新文件组中创建具有相同结构的第二个表，复制数据，删除旧表并重命名新表。然而，这会带来各种可能的问题，例如丢失数据、无效数据（由于缺少检查约束）和错误处理非常困难。我过去曾为一张桌子做过这个，但恕我直言，它的扩展性不好，考虑到必须传输 100 张桌子的噩梦，你得到了要修复的桌子 15、33、88 和 99 的错误。

    因此，我使用了一个众所周知的分区技巧：正如Kimberly Tripp所描述的那样，当您对它进行分区时，LOB-Data 确实会移动到新的文件组。由于我不打算长期使用分区，而只是作为移动 LOB 的助手，分区方案非常乏味（将所有分区放入一个文件组）：我什至不关心数据在哪个分区上因为我只是想让他们感动。实际上，这种技术和实现并不是我自己发明的……我使用了Mark White的强大脚本。我的错误是没有完全理解这个脚本的作用和含义……我现在有：

    对于 LOB-Data，有必要重建（或重新创建）表（主要是聚集索引）两次：第一次是在其上放置分区，第二次是删除分区。无论您使用SORT_IN_TEMPDB=ON与否，这都会导致必须提供原始数据的空间两次：如果您的原始表有 100MB，则需要提供 200MB 才能使操作成功。一开始我很疑惑，结果我的新数据文件在操作完成后有很多可用空间。

    现在我接受了我不能绕过空闲空间作弊的事实。但是我可以避免事后缩小文件的必要性。因此，我的解决方案是在临时文件组上进行第一次重建，在目标文件组上进行第二次重建（删除分区）。之后可以删除临时文件组（如果我希望不再出现错误消息“无法删除文件组”（请在此处查看我的问题））。

    感谢阅读和帮助

    马丁

    这是我的问题的重现脚本，其中包括我为它提出的解决方案：

        /*============================================================================
  Adapted the following file published by sqlskills to demonstrate filegrowth
  after partitioning for StackOverflow Question.

  Martin Guth, 02.02.2018

  File:     MovingLOBData.sql

  Summary:  Because 2012 supports online index rebuilds - even with LOB.
            You might think this means you can move LOB data around 
            (one of the VERY cool things you can do with IN_ROW data to 
            actually move it). However, the behavior of LOB data is NOT
            necessarily intuitive. This script will show you how/why/what!

  SQL Server Versions: SQL Server 2012
------------------------------------------------------------------------------
  Written by SQLskills.com

  (c) SQLskills.com. All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

SET NOCOUNT ON
go

USE master
go


CREATE DATABASE [TestLOB]
 CONTAINMENT = NONE
ON  PRIMARY 
(   NAME = N'TestLOBPrimary'
    , FILENAME = N'U:\DB_DATA\TestLOBPrimary.mdf' 
    , SIZE = 100MB , FILEGROWTH = 1024KB ), 

FILEGROUP [FG1] 
(   NAME = N'FG1File1'
    , FILENAME = N'U:\DB_DATA\FG1File1.ndf' 
    , SIZE = 40MB , FILEGROWTH = 20480KB ), 
(   NAME = N'FG1File2'
    , FILENAME = N'U:\DB_DATA\FG1File2.ndf' 
    , SIZE = 40MB , FILEGROWTH = 20480KB ), 

FILEGROUP [FG2] 
(   NAME = N'FG2File1'
    , FILENAME = N'U:\DB_DATA\FG2File1.ndf' 
    , SIZE = 20MB , FILEGROWTH = 0MB ), 
(   NAME = N'FG2File2'
    , FILENAME = N'U:\DB_DATA\FG2File2.ndf' 
    , SIZE = 20MB , FILEGROWTH = 0MB ),

FILEGROUP [tempLOB] 
(   NAME = N'tempLOB1'
    , FILENAME = N'U:\DB_DATA\templob1.ndf' 
    , SIZE = 20MB , FILEGROWTH = 0MB ), 
(   NAME = N'tempLOB2'
    , FILENAME = N'U:\DB_DATA\templob2.ndf' 
    , SIZE = 20MB , FILEGROWTH = 0MB )

LOG ON 
(   NAME = N'TestLOBLog'
    , FILENAME = N'U:\DB_DATA\TestLOBLog.ldf' 
    , SIZE = 10MB , FILEGROWTH = 10MB)
GO

USE TestLOB
go

ALTER DATABASE TestLOB 
MODIFY FILEGROUP FG1 DEFAULT
go

--DROP TABLE TestLobTable;

CREATE TABLE dbo.TestLobTable
(
    c1  int identity,
    c2  char(8000)      default 'this is a test',
    c3  varchar(max)    NULL
) -- will be created on FG1
go



INSERT INTO dbo.TestLobTable 
( 
    c2, 
    c3
)
VALUES
(
    'this is a test',
    REPLICATE (convert(varchar(max), 'ABC'), 8000) 
)
go 1000 

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL 
ON TestLobTable (c1)
go


sp_blitzIndex
    @databaseName = 'TestLOB',
    @schemaName = 'dbo',
    @tableName = 'TestLobTable'

go

-- size is roughly 40 MB: 1,000 rows; 39.2MB; 31.3MB LOB


SELECT 
    f.name AS [filename], 
    fu.total_page_count AS [pageCount], 
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  2.875000
FG1File1    5120    40.000000   19.687500
FG1File2    5120    40.000000   19.687500
FG2File1    2560    20.000000   0.062500
FG2File2    2560    20.000000   0.062500


--> 2*19,687 MB are occupied in Filegroup 1 ---> approx 40 MB in total in Filegroup 1
*/


/* moving Lob data using partitioning trick */
CREATE PARTITION FUNCTION PF_TestLobTable (int)
AS RANGE RIGHT FOR VALUES (0)
go


CREATE PARTITION SCHEME PS_TestLobTable 
AS PARTITION PF_TestLobTable
TO ( fg2, fg2 )
go


CREATE UNIQUE CLUSTERED INDEX TestLobTableCL 
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB= OFF)
ON PS_TestLobTable (c1)
go



SELECT 
    f.name AS [filename], 
    fu.total_page_count AS [pageCount], 
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    2688    21.000000   19.750000
FG2File2    2688    21.000000   19.687500


--> now Filegroup 2 has roughly 40 MB data...interestingly the create index would fail if having 2*20MB capacity available but would pass at 2*21MB
*/

-- try to recreate the index again to get rid of partitioning
CREATE UNIQUE CLUSTERED INDEX TestLobTableCL 
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB= OFF)
ON [FG2]
go

/* error message 1105
Could not allocate space for object 'dbo.TestLobTable'.'TestLobTableCL' in database 'TestLOB' because the 'FG2' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

--> makes sense because no free space available in filegroup with partitioned clustered index already present
*/


ALTER DATABASE TestLOB MODIFY FILE (NAME = N'FG2File1', SIZE=41MB); 
ALTER DATABASE TestLOB MODIFY FILE (NAME = N'FG2File2', SIZE=41MB); 

-- rebuild again without sort in tempdb
CREATE UNIQUE CLUSTERED INDEX TestLobTableCL 
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON,  SORT_IN_TEMPDB= OFF)
ON [FG2]
go

SELECT 
    f.name AS [filename], 
    fu.total_page_count AS [pageCount], 
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    5248    41.000000   19.750000
FG2File2    5248    41.000000   19.625000


--> now the files of FG2 have 50% free space left
*/

-- try to shrink with truncateonly
DBCC SHRINKFILE('FG2File1', TRUNCATEONLY);
DBCC SHRINKFILE('FG2File2', TRUNCATEONLY);


SELECT 
    f.name AS [filename], 
    fu.total_page_count AS [pageCount], 
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    5048    39.437500   19.750000
FG2File2    5040    39.375000   19.625000

--> no significant effect...still almost 50% free space
*/



-- recreate the table
DROP PARTITION SCHEME PS_TestLobTable;
DROP PARTITION FUNCTION PF_TestLobTable;


DROP TABLE TestLobTable;

CREATE TABLE dbo.TestLobTable
(
    c1  int identity,
    c2  char(8000)      default 'this is a test',
    c3  varchar(max)    NULL
) -- will be created on FG1
go



INSERT INTO dbo.TestLobTable 
( 
    c2, 
    c3
)
VALUES
(
    'this is a test',
    REPLICATE (convert(varchar(max), 'ABC'), 8000) 
)
go 1000 

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL 
ON TestLobTable (c1)
go


sp_blitzIndex
    @databaseName = 'TestLOB',
    @schemaName = 'dbo',
    @tableName = 'TestLobTable'
GO



SELECT 
    f.name AS [filename], 
    fu.total_page_count AS [pageCount], 
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.125000
FG1File1    5120    40.000000   19.687500
FG1File2    5120    40.000000   19.687500
FG2File1    5048    39.437500   0.062500
FG2File2    5040    39.375000   0.062500

--> data on filegroup 1 again... move them to filegroup2 this time with SORT_IN_TEMPDB
*/

/* moving Lob data using partitioning trick */
CREATE PARTITION FUNCTION PF_TestLobTable (int)
AS RANGE RIGHT FOR VALUES (0)
go


CREATE PARTITION SCHEME PS_TestLobTable 
AS PARTITION PF_TestLobTable
TO ( fg2, fg2 )
go


CREATE UNIQUE CLUSTERED INDEX TestLobTableCL 
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB = ON )
ON PS_TestLobTable (c1)
go

-- removing partitioning on table
CREATE UNIQUE CLUSTERED INDEX TestLobTableCL 
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB = ON )
ON [fg2]
go


-- now try to shrink with truncateonly
DBCC SHRINKFILE('FG2File1', 20,TRUNCATEONLY);
DBCC SHRINKFILE('FG2File2', 20,TRUNCATEONLY);


SELECT 
    f.name AS [filename], 
    fu.total_page_count AS [pageCount], 
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    5376    42.000000   19.687500
FG2File2    5376    42.000000   19.687500

--> no significant effect...still almost 50% free space
*/


--- retry with separate filegroup


CREATE DATABASE [TestLOB]
 CONTAINMENT = NONE
ON  PRIMARY 
(   NAME = N'TestLOBPrimary'
    , FILENAME = N'U:\DB_DATA\TestLOBPrimary.mdf' 
    , SIZE = 100MB , FILEGROWTH = 1024KB ), 

FILEGROUP [FG1] 
(   NAME = N'FG1File1'
    , FILENAME = N'U:\DB_DATA\FG1File1.ndf' 
    , SIZE = 40MB , FILEGROWTH = 20480KB ), 
(   NAME = N'FG1File2'
    , FILENAME = N'U:\DB_DATA\FG1File2.ndf' 
    , SIZE = 40MB , FILEGROWTH = 20480KB ), 

FILEGROUP [FG2] 
(   NAME = N'FG2File1'
    , FILENAME = N'U:\DB_DATA\FG2File1.ndf' 
    , SIZE = 20MB , FILEGROWTH = 0MB ), 
(   NAME = N'FG2File2'
    , FILENAME = N'U:\DB_DATA\FG2File2.ndf' 
    , SIZE = 20MB , FILEGROWTH = 0MB ),

FILEGROUP [tempLOB] 
(   NAME = N'tempLOB1'
    , FILENAME = N'U:\DB_DATA\templob1.ndf' 
    , SIZE = 20MB , FILEGROWTH = 0MB ), 
(   NAME = N'tempLOB2'
    , FILENAME = N'U:\DB_DATA\templob2.ndf' 
    , SIZE = 20MB , FILEGROWTH = 0MB )

LOG ON 
(   NAME = N'TestLOBLog'
    , FILENAME = N'U:\DB_DATA\TestLOBLog.ldf' 
    , SIZE = 10MB , FILEGROWTH = 10MB)
GO

USE TestLOB
go

ALTER DATABASE TestLOB 
MODIFY FILEGROUP FG1 DEFAULT
go

--DROP TABLE TestLobTable;

CREATE TABLE dbo.TestLobTable
(
    c1  int identity,
    c2  char(8000)      default 'this is a test',
    c3  varchar(max)    NULL
) -- will be created on FG1
go



INSERT INTO dbo.TestLobTable 
( 
    c2, 
    c3
)
VALUES
(
    'this is a test',
    REPLICATE (convert(varchar(max), 'ABC'), 8000) 
)
go 1000 

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL 
ON TestLobTable (c1)
go


sp_blitzIndex
    @databaseName = 'TestLOB',
    @schemaName = 'dbo',
    @tableName = 'TestLobTable'

go

-- size is roughly 40 MB: 1,000 rows; 39.2MB; 31.3MB LOB


SELECT 
    f.name AS [filename], 
    fu.total_page_count AS [pageCount], 
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  2.875000
FG1File1    5120    40.000000   19.687500
FG1File2    5120    40.000000   19.687500
FG2File1    2560    20.000000   0.062500
FG2File2    2560    20.000000   0.062500


--> 2*19,687 MB are occupied in Filegroup 1 ---> approx 40 MB in total in Filegroup 1
*/

/* moving Lob data using partitioning trick */
CREATE PARTITION FUNCTION PF_TestLobTable (int)
AS RANGE RIGHT FOR VALUES (0)
go


CREATE PARTITION SCHEME PS_TestLobTable 
AS PARTITION PF_TestLobTable
TO ( tempLOB, tempLOB )
go


CREATE UNIQUE CLUSTERED INDEX TestLobTableCL 
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON)
ON PS_TestLobTable (c1)
go


SELECT 
    f.name AS [filename], 
    fu.total_page_count AS [pageCount], 
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    2560    20.000000   0.062500
FG2File2    2560    20.000000   0.062500
tempLOB1    2560    20.000000   19.687500
tempLOB2    2560    20.000000   19.687500


--> 2*19,687 MB are occupied in Filegroup 1 ---> approx 40 MB in total in Filegroup 1
*/



CREATE UNIQUE CLUSTERED INDEX TestLobTableCL 
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON)
ON [FG2]
go


SELECT 
    f.name AS [filename], 
    fu.total_page_count AS [pageCount], 
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    2560    20.000000   19.625000
FG2File2    2560    20.000000   19.750000
tempLOB1    2560    20.000000   0.062500
tempLOB2    2560    20.000000   0.062500


--> data successfully moved to fg2, tempLOB empty
*/
DROP PARTITION SCHEME PS_TestLobTable 
DROP PARTITION FUNCTION PF_TestLobTable 

ALTER DATABASE TestLOB REMOVE FILE tempLOB1;
ALTER DATABASE TestLOB REMOVE FILE tempLOB2;
ALTER DATABASE TestLOB REMOVE FILEGROUP tempLOB;

/*
    summary:
    - Moving LOB Data with the help of partitioning results in twice the space needed of the original data for temporary rebuilds.
    - To avoid problematic and long running shrinking of database files it's best to use a different filegroup for the first rebuild as this can be easily removed afterwards.
*/
    • 0

相关问题