主页 / dba / 问题 / 196719
Accepted
Martin Guth
Asked: 2018-02-01 05:33:49 +0800 CST

无法删除没有关联文件的文件组

  • 772

我在 SQL Server 2017 CU3 上遇到了一些奇怪的错误消息。我正在迁移数据库并重新组织文件组。“重组”是指我使用存储过程在新文件组上为对象创建分区函数和分区方案，在分区时重建索引，然后删除分区。

最后我得到了一些空文件组。他们的文件被删除。文件组本身也被删除。这在大多数情况下都很有效。但是对于两个数据库，我删除了文件...留下一个文件组，但没有关联文件

ALTER DATABASE REMOVE FILEGROUP

引发错误 5042：

无法删除文件组“xyz”，因为它不为空。

问题

我怎样才能摆脱那个空文件组......可能是什么问题？

我已经阅读了一些常见问题，但它们在我的系统中不存在：

  • 检查：

    SELECT * FROM sys.partition_schemes;
SELECT * FROM sys.partition_functions;

    0 行...数据库中没有剩余分区对象

  • UPDATE STATISTICS对于数据库中的所有对象

    没有效果

  • 检查文件组上的索引：

    SELECT * FROM  sys.data_spaces ds
INNER JOIN sys.indexes i
ON ds.data_space_id = i.data_space_id
WHERE ds.name = 'xyz'

    0 行

  • 检查文件组中的对象：

    SELECT
    au.*,
    ds.name AS [data_space_name],
    ds.type AS [data_space_type],
    p.rows,
    o.name AS [object_name]
FROM sys.allocation_units au
    INNER JOIN sys.data_spaces ds
        ON au.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p
        ON au.container_id = p.partition_id
    INNER JOIN sys.objects o
        ON p.object_id = o.object_id
WHERE au.type_desc = 'LOB_DATA'
AND ds.name ='xyz'

    0 行

在从文件组中删除文件之前，我还尝试了DBCC SHRINKFILE参数。EMPTYFILE这对我来说真的没有意义，但是我阅读了将其描述为修复的解决方案。反正没有效果。

我有一些希望阅读有关服务器故障的这个问题并尝试了以下方法：

  • 更新所有统计信息
  • 删除所有与索引无关的统计信息

然而，这没有任何效果。我仍然有一个没有关联文件的文件组，并且无法删除该文件组。我完全感到困惑，因为这发生在某些数据库中而不是其他数据库中（具有相同的结构）。当我DBCC CHECK FILEGROUP在这个空文件组上执行时，我收到一堆错误消息，如下所示：

无法处理对象“STORY_TRANSLATIONSCCC”（ID 120387498）、索引“Ref90159CCC”（ID 2）的行集 ID 72057594712162304，因为它位于未检查的文件组“CCC_APPLICATION_new”（ID 8）上。

“STORY_TRANSLATIONSCCC”的 DBCC 结果。对象“STORY_TRANSLATIONSCCC”的 0 页中有 0 行。

这是正常的还是它指向不寻常的事情？

这个问题可能是重复的，但是我在 dba.stackexchange 上的其他问题中找不到适合我的解决方法。请查看我已经尝试过的列表。这与无法删除未使用的文件组中描述的解决方案相同。

更多细节

也许它有助于理解我在错误发生之前做了什么。我正在计划迁移到新服务器。我目前正在测试实例上对此进行测试。数据库从 prod 服务器恢复，恢复模式切换为简单。我的目标是重组文件组并从每个文件组一个文件的模型转移到每个文件组两个文件的模型。为了实现这一点，我创建了新的空文件组，每个文件组有两个文件，然后将数据移过来。不幸的是，大多数对象都有 LOB 数据（XML 和二进制）......所以我也利用分区作为帮助来移动 lob 数据。最后，所有数据都驻留在新文件组中，旧文件组为空。然后我删除所有文件并删除相应的文件组。主文件组仍然存在，只是添加了另一个文件。我的问题。此过程工作正常，但在两个数据库中可以删除文件，但不能删除文件组。令人惊讶的是，这些数据库的结构应该与其他数据库的结构相同，在移动数据和删除旧文件组的过程中没有遇到任何问题。

因此，这是出现问题的两个数据库的文件组和文件的列表：

  1. CCC_GENTE

前

+-----------------+------------+
| Filegroup       | Filename   |
+-----------------+------------+
| CCC_APPLICATION | CCC_APP    |
+-----------------+------------+
| CCC_ARCHIVE     | CCC_ARCHIV |
+-----------------+------------+
| CCC_AXN         | CCC_AXN    |
+-----------------+------------+
| CCC_GDV         | CCC_GDV    |
+-----------------+------------+
| PRIMARY         | CCC        |
+-----------------+------------+

后

    +-----------------+--------------------------+--------------------+----------------------------------------------------+
| Filegroup name  | Filegroup temporary name | Filename (logical) | Status                                             |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_APPLICATION | -                        | CCC_APP            | file removed, filegroup  cannot be removed (error) |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_ARCHIVE     | -                        | CCC_ARCHIV         | file and filegroup removed                         |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_AXN         | -                        | CCC_AXN            | file and filegroup removed                         |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_GDV         | -                        | CCC_GDV            | file and filegroup removed                         |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| PRIMARY         | -                        | CCC                | file renamed to PRIMARY_1                          |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| PRIMARY         | -                        | PRIMARY_2          | new file added                                     |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_APPLICATION | CCC_APPLICATION_new      | CCC_APPLICATION_1  | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_APPLICATION | CCC_APPLICATION_new      | CCC_APPLICATION_2  | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_ARCHIVE     | CCC_ARCHIVE_new          | CCC_ARCHIVE_1      | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_ARCHIVE     | CCC_ARCHIVE_new          | CCC_ARCHIVE_2      | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_AXN         | CCC_AXN_new              | CCC_AXN_1          | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_AXN         | CCC_AXN_new              | CCC_AXN_2          | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_GDV         | CCC_GDV_new              | CCC_GDV_1          | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_GDV         | CCC_GDV_new              | CCC_GDV_2          | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+

我希望这会有所帮助。还有第二个数据库，其中文件组名称不同，但为简洁起见，我将其省略。

sql-server filegroups

2 个回答

  1. 仔细检查数据库中的文件组

    通过发出以下命令验证文件组没有任何附加文件：

    use [DB]
go
sp_helpfilegroup

    这将产生一个文件组列表：

     groupname | groupid | filecount
-----------+---------+-----------
 PRIMARY   | 1       | 1
 xyz       | 2       | 1

    ...然后对于列出的每个文件组执行

    use [DB]
go
sp_helpfilegroup @filegroupname='PRIMARY'
go
sp_helpfilegroup @filegroupname='xyz'

    输出可能如下所示：

     groupname | groupid | filecount
-----------+---------+------------
 xyz       | 2       | 1

    ....第二个输出可能是：

      file_in_group    | fileid | filename                          | size    | maxsize   | growth  
 ------------------+--------+-----------------------------------+---------+-----------+---------
  xyz_logical_name | 3      | X:\SQL\SQL_DATA\xyz_filegroup.ndf | 5120 KB | Unlimited | 1024 KB

    删除文件组

    如果您仍然有一个与您的文件组之一关联的文件，那么删除文件组的逻辑文件和文件组本身的完整命令将是：

    USE [DB]
GO
ALTER DATABASE [DB] REMOVE FILE [xyz_logical_name]
GO
ALTER DATABASE [DB] REMOVE FILEGROUP [xyz]
GO

    文件组“xyz”是默认值

    如果您在尝试删除文件组的逻辑文件时收到如下所示的错误消息：

    Msg 5031, Level 16, State 1, Line 88
Cannot remove the file 'xyz_logical_name' because it is the only file in the DEFAULT filegroup.

    ...那么您必须将文件组设置为PRIMARY文件DEFAULT组：

    ALTER DATABASE [DB] MODIFY FILEGROUP [PRIMARY] DEFAULT

    文件组“xyz”是只读的

    但是，如果错误消息如下：

    Msg 5055, Level 16, State 2, Line 88 
Cannot add, remove, or modify file 'xyz_logical_name'. The file is read-only.

    ...那么您将不得不删除xyz文件组上的 READ_ONLY 属性：

    ALTER DATABASE [DB] MODIFY FILEGROUP [xyz] READWRITE

    您现在应该能够删除文件组的逻辑文件和文件组本身。

    未结交易

    如果您确实没有与xyz您要删除的文件组关联的文件 (logical_name / pyhsical_file_name)，那么执行事务日志备份可能会释放任何阻碍进一步删除文件组的事务。

    拨打 911

    如果一切都失败了，您可能需要考虑与 Microsoft 通话。

    元数据不匹配

    进一步研究后添加

    显然，在某些情况下，数据库中的元数据不能反映对象的实际位置。

    参考：
    - FIX：切换表分区并删除相应文件和文件组后元数据不一致错误（Microsoft 支持）
    - FIX：当您尝试在 SQL Server 中删除或删除文件组或分区方案和函数时发生错误（Microsoft 支持）

    这两种情况似乎分别通过SQL Server 2014 SP1 的累积更新 3和SQL Server 2016 的累积更新 1得到解决。它们不适用于您的情况，但它们表明有时元数据可能是错误的。

    似乎阻止您的文件组删除的项目是索引，它可能存储有错误的元数据。

    可能的解决方案

    考虑重建Ref90159CCC错误消息中引用的索引。

    Cannot process rowset ID 72057594712162304 of object 
"STORY_TRANSLATIONSCCC" (ID 120387498), index "Ref90159CCC" (ID 2), 
because it resides on filegroup "CCC_APPLICATION_new" (ID 8), 
which was not checked.

    以下文章描述了类似的情况，并展示了作者如何发现罪魁祸首并解决了这种情况。

    参考：SQL Server：切换分区和元数据不一致问题（博客dbi-services.com）

    查找与过时文件组相关的对象

    我装配了这个脚本来检查表/索引/分区/等的尽可能多的隐藏位置。这可能仍与删除的文件组文件有关：

    请替换DEFAULTRO为您过时的文件组的名称（例如CCC_APPLICATION）

     /* ==================================================================
  Author......: hot2use
  Date........: 16.02.2018
  Version.....: 0.1
  Server......: LOCALHOST (first created for)
  Database....: StackExchange
  Owner.......: -
  Table.......: -
  Type........: Script
  Name........: ADMIN_Filegroup_Statement_All_Objects.sql
  Description.: Checks all objects related to filegroups based on the 
  ............  relationship between the data_space_id ID.
  ............      
  History.....:  0.1    h2u First created
  ............      
  ............      
 ================================================================== */
DECLARE @nvObsoleteFG AS NVARCHAR(50)
SET @nvObsoleteFG = N'DEFAULTRO'

SELECT -- DISTINCT use in conjunction with sys.allocation_units table and objects
       '-->'                            AS DataSpaceNfo
      ,ds.name                          AS DataSpaceName
      ,ds.data_space_id                 AS DatSpacID_DataSpace
      ,'-->'                            AS FileGroupNfo
      ,f.name                           AS FileGrpName
      ,f.data_space_id                  AS DatSpacID_FileGrp
      ,f.[type]                         AS FileGrpType
      ,'-->'                            AS DataBaseFilesNfo
      ,df.data_space_id                 AS DatSpacID_DBFiles
      ,df.[type]                        AS DBFilesType
      ,df.name                          AS DBFilesName
      ,'-->'                            AS ObjectNfo
      ,o.[object_id]                    AS OjbID
      ,o.name                           AS ObjName4HeapsClusters
      ,o.type_desc                      AS ObjTypeDesc
      ,'-->'                            AS IndexNfo
      ,i.name                           AS ObjName4Indexes
      ,i.type_desc                      AS IndTypeDesc
      ,i.[object_id]                    AS IndObjID
      ,i.index_id                       AS IndIndID
      ,'-->'                            AS PartSchemaNfo
      ,ps.name                          AS PartSchemaName
      ,ps.data_space_id                 AS DatSpacID_PartSchema
       -- ,au.type_desc                     AS AllocUnitTypeDesc
       -- ,au.data_space_id                 AS DatSpacID_AllocUnit
FROM   sys.data_spaces                  AS ds
       FULL JOIN sys.filegroups         AS f
            ON  ds.data_space_id = f.data_space_id
       FULL JOIN sys.database_files     AS df
            ON  f.data_space_id = df.data_space_id
       FULL JOIN sys.indexes            AS i
            ON  f.data_space_id = i.data_space_id
       FULL JOIN sys.partition_schemes  AS ps
            ON  f.data_space_id = ps.data_space_id
       FULL JOIN sys.objects            AS o
            ON  i.[object_id] = o.[object_id]         
       -- FULL JOIN sys.allocation_units   AS au
       --      ON  au.data_space_id = f.data_space_id

-- If you omit the whole WHERE clause you get an overview of everything (incl. MS objects)
WHERE  o.is_ms_shipped = 0
       -- if you omit the lower AND you'll get all items related to all filegroups
       AND (
               df.data_space_id=(
                   SELECT data_space_id
                   FROM   sys.filegroups
                   WHERE  NAME = @nvObsoleteFG
               )
               OR f.data_space_id=(
                      SELECT data_space_id
                      FROM   sys.filegroups
                      WHERE  NAME = @nvObsoleteFG
                  ) 
               OR df.data_space_id=(
                      SELECT data_space_id
                      FROM   sys.filegroups
                      WHERE  NAME = @nvObsoleteFG
                  )
               OR ps.data_space_id=(
                      SELECT data_space_id
                      FROM   sys.filegroups
                      WHERE  NAME = @nvObsoleteFG
                  )
           )

    参考：我的个人剧本

    运行它并查看是否显示包含过时文件组的任何对象。使用data_space_id而不是使用名称。连接是有意FULL捕获任何“孤立”引用。

    或者使用这个较小的脚本来快速检查过时文件组中的项目：

    SELECT o.[name]
      ,o.[type]
      ,i.[name]
      ,i.[index_id]
      ,f.[name]
FROM   sys.indexes i
       INNER JOIN sys.filegroups f
            ON  i.data_space_id = f.data_space_id
       INNER JOIN sys.all_objects o
            ON  i.[object_id] = o.[object_id]
WHERE  i.data_space_id = f.data_space_id
       AND o.type = 'U' -- User Created Tables

    参考：SQL SERVER – 列出在数据库中所有文件组上创建的所有对象(SQLAuthority.com)

    • 8
  2. Best Answer

    四个月后，Microsoft 支持人员找到了解决方案。确实有一个表引用了这个大概是空的文件组。

    该表由以下语句标识：

    SELECT t.[name] FROM sys.tables t
   inner join sys.filegroups f
         on t.lob_data_space_id = f.data_space_id
   where f.name = 'xyz'

    将数据移动到新表并删除有问题的表后，文件组已成功删除。移动数据的过程是：创建一个具有相同结构和索引的新表，通过 SELECT INTO 复制数据，删除旧表，重命名新表（当然，如果整个过程中有外键，请注意） )

    • 3

相关问题