How to remove unallocated space after partitioning

Question

I've recently done partitioning on a table (postcode column) and created new filegroups for each postcode.

Now that it is no longer using the PRIMARY filegroup, the file size is still as it is. How do I reduce it or shrink it as it appears that there is a huge unallocated space?

I attached a screenshot of the used capacity of the file.

I would appreciate it if someone could tell me the way to do it (Shrinking the PRIMARY filegroup or reducing the space that was for the data when it was on the PRIMARY filegroup) without impacting any of the rest of the data.

Answer 1

Here some scripts for you to double check, how much space is allocated to each filegroup and then on the second script all the tables in each file group and how much space they are using.

from there you can see what is inside PRIMARY.

The shrinking process will not delete any data, so in my opinion you need to see in detail what is in there first.

then if that is all good then have a look at this:

You need to provision some more space before you can shrink the old files, but it’s a much cleaner mechanism.

this script shows how the disk space is allocated in each filegroup

    SELECT 
    DatabaseName=DB_NAME() 
    ,FG.groupid 
    ,FG.groupname 
    ,fileid 
    ,convert(decimal(12,2),round(F.size/128.000,2)) as file_size 
    ,convert(decimal(12,2),
     round(fileproperty(F.name,'SpaceUsed')/128.000,2)) as space_used 
    ,convert(decimal(12,2),
     round((F.size-fileproperty(F.name,'SpaceUsed'))/128.000,2)) as free_space 
    ,F.name 
    ,F.filename 
FROM sys.sysfiles F
LEFT OUTER JOIN sys.sysfilegroups FG
    ON F.groupid = FG.groupid

all the tables in each file group and how much space they are using.

select * from
( 
SELECT 
    s.Name  + N'.' + t.NAME AS TableName,
    d.name as Filegroup,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) 
        AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) 
        AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) 
                - SUM(a.used_pages)) * 8) / 1024.00, 2) 
        AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.data_spaces d  ON i.data_space_id = d.data_space_id
 
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    d.name,t.Name, s.Name, p.Rows
--ORDER BY 
--    t.Name

    ) radhe
order by UsedSpaceMB desc

After all that try this:

Go to database properties - files

when you change the initial file name - in my case I changed it from 1024 to 80

generate the script script instead of just clicking ok

I get this script:

USE [yourDB]
GO
DBCC SHRINKFILE (N'YourDBDataPrimary' , 80)
GO

then you can go back do what you were doing, just instead of clicking ok generate the script:

you will get something like this:

USE [yourDB]
GO
DBCC SHRINKFILE (N'YourDBDataPrimary' , 0, TRUNCATEONLY)
GO

then you go look at the file and you see 80MB