0

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

4
  • If sys.allocation_units shows 30MB, surely you can trace that to objects in the database? (Don't forget to check sys.all_objects.) Andy recently talked about a few extra steps he followed to properly empty tempdb files, maybe something there will help. Aug 13 '20 at 2:08
  • When I join sys.allocation_units to sys.partitions I get 0 rows
    – HandyD
    Aug 13 '20 at 4:49
  • Joined how? Did you try with hobt / container id? Did you check the page allocations DMF? See mssqltips.com/sqlservertip/6388/… Aug 13 '20 at 5:24
  • I tried on both hobt_id and container_id, no luck. I did eventually identify the underlying objects using DBCC PAGE. I still don't know why allocation_units couldn't be joined correctly to sys.partitions, but at least having identified the objects I was able to clean up the data and remove the files now.
    – HandyD
    Sep 2 '20 at 3:02
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.