SQL Server – Finding Data in File or Filegroup

sql server

I am having an issue trying to migrate data out of a filegroup to empty and remove the filegroup. I've moved all indexes/tables and all partition schemes using the filegroup to the new filegroup, but I cannot remove the last file from the old filegroup. The error I get is that the file isn't empty.

I've tried the EMPTYFILE trick but this doesn't work as it says there is still data in the file, which seems to be true as querying sys.allocation_units shows around 30 MB of data still in the file. Querying index, partition, object and other DMVs doesn't report anything still using this filegroup as data space.

What other DMVs/DMFs are there to show me what the data left in the file actually is?

SQL Server 2016 Enterprise Edition

Best Answer

So I was never able to determine why the allocated pages couldn't be linked to the underlying object using sys.allocation_units, however, I was eventually able to identify the objects using a modified version of a script from Paul Randal and DBCC PAGE.

Firstly, running this script (which is based on Paul Randal's sp_AllocationMetadata but modified to filter based on filegroup name and not object name):

SELECT
    [sa].[allocation_unit_id] AS [Alloc Unit ID],
    [sa].[type_desc] AS [Alloc Unit Type],
    '(' + CONVERT (VARCHAR (6),
        CONVERT (INT,
            SUBSTRING ([sa].[first_page], 6, 1) +
            SUBSTRING ([sa].[first_page], 5, 1))) +
    ':' + CONVERT (VARCHAR (20),
        CONVERT (INT,
            SUBSTRING ([sa].[first_page], 4, 1) +
            SUBSTRING ([sa].[first_page], 3, 1) +
            SUBSTRING ([sa].[first_page], 2, 1) +
            SUBSTRING ([sa].[first_page], 1, 1))) +
    ')' AS [First Page],
    '(' + CONVERT (VARCHAR (6),
        CONVERT (INT,
            SUBSTRING ([sa].[root_page], 6, 1) +
            SUBSTRING ([sa].[root_page], 5, 1))) +
    ':' + CONVERT (VARCHAR (20),
        CONVERT (INT,
            SUBSTRING ([sa].[root_page], 4, 1) +
            SUBSTRING ([sa].[root_page], 3, 1) +
            SUBSTRING ([sa].[root_page], 2, 1) +
            SUBSTRING ([sa].[root_page], 1, 1))) +
    ')' AS [Root Page],
    '(' + CONVERT (VARCHAR (6),
        CONVERT (INT,
            SUBSTRING ([sa].[first_iam_page], 6, 1) +
            SUBSTRING ([sa].[first_iam_page], 5, 1))) +
    ':' + CONVERT (VARCHAR (20),
        CONVERT (INT,
            SUBSTRING ([sa].[first_iam_page], 4, 1) +
            SUBSTRING ([sa].[first_iam_page], 3, 1) +
            SUBSTRING ([sa].[first_iam_page], 2, 1) +
            SUBSTRING ([sa].[first_iam_page], 1, 1))) +
    ')' AS [First IAM Page]
FROM sys.system_internals_allocation_units AS [sa] (NOLOCK)
INNER JOIN sys.allocation_units au (NOLOCK) ON au.allocation_unit_id = sa.allocation_unit_id
INNER JOIN sys.data_spaces ds (NOLOCK) ON ds.data_space_id = au.data_space_id
WHERE ds.[name] = '<filegroup name here>'

The output gave me a list of allocation units and the associated page identifiers:

Alloc Unit ID       Alloc Unit Type First Page      Root Page   First IAM Page
72059231029821440   IN_ROW_DATA     (44:2579440)    (21:2832)   (21:2812)
72059231073271808   IN_ROW_DATA     (25:4532384)    (21:3888)   (21:3883)
72059240244248576   IN_ROW_DATA     (21:656)        (21:656)    (21:1040)
72059240310046720   IN_ROW_DATA     (44:1047088)    (21:2592)   (21:2572)
72059240312340480   IN_ROW_DATA     (25:2516760)    (21:1880)   (21:1859)

These results can then be checked against DBCC PAGE to identify the underlying object:

DBCC TRACEON (3604);
DBCC PAGE (19, 21, 688, 0);
DBCC TRACEOFF (3604);

This outputs some Metadata, specifically a line that says Metadata: ObjectId = <object id>. This object ID references the object this data page is related to.

In my case, I had around 300 allocation units to work through. The concerning part is the objects identified were built on partition schemes that never referenced the filegroup in question as a destination data space, but somehow the data ended up allocated in these files. In any case, rebuilding the indexes deallocated the pages from the filegroup in question and the files and filegroup could be removed.

Related Question