October 21, 2022 at 3:59 pm
Hello,
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.
October 21, 2022 at 4:14 pm
shrink as you see on your image. and rebuild indexes afterwards of tables that are on that filegroup.
October 21, 2022 at 4:25 pm
Will shrinking the filegroup this way cause fragmentation issues? I just want to know the safest way to do it.
Thank you.
October 21, 2022 at 4:27 pm
Shrink only the file(s) that are part of the PRIMARY filegroup:
DBCC SHRINKFILE(1, 512)
DBCC SHRINKFILE(?, 512) --if you have additional file(s) in the PRIMARY fg
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 21, 2022 at 4:45 pm
Will shrinking the filegroup this way cause fragmentation issues? I just want to know the safest way to do it.
Thank you.
yes - and that is WHY I said to rebuild indexes after you done it.
and there is no other easy way to do it - other than create a brand new temporary filegroup, rebuild all your tables and indexes from primary onto that one, shrink the primary group, and rebuild again into the primary group and then drop the temporary filegroup
October 21, 2022 at 5:53 pm
It may fragment some indexes and not others. You certainly don't want to automatically rebuild every index. Also, rebuild from smallest to largest of the indexes that do need rebuilt.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 17, 2022 at 7:36 am
It may fragment some indexes and not others. You certainly don't want to automatically rebuild every index. Also, rebuild from smallest to largest of the indexes that do need rebuilt.
Lookup the DMF named "sys.dm_db_index_physical_stats" on Microsoft Docs. Use this to look for candidate indexes to rebuild, those with high fragmentation.
----------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply