March 16, 2012 at 3:28 pm
sbaker-757360 (3/16/2012)
The problem filegroup (set as default) which I will call Filegroup1 only had one ndf file in it.
what is the name of this filegroup within SQL Server, this is important to know.
sbaker-757360 (3/16/2012)
So all 50 table objects in the database are in FileGroup1File1 minus the big table that I moved to a new file group.
OK, so you still have 50 tables and any indexes they have in the filegroup\file you wish to shrink, correct?
sbaker-757360 (3/16/2012)
I created a new file in FileGroup1 to perform an EMPTYFILE, which is now 92% complete.
Personally that's just compounding the problem and making harder to troubleshoot. Have you queried the system tables to find out the sum size of the 50 tables that are left in the filegroup\file?
Use this query to find this information
SELECT filegroup_name(au.data_space_id) as FGName, o.name AS table_name, p.index_id, --i.name AS index_name,
au.type_desc AS allocation_type,
au.total_Pages * 8 as TotKB, au.used_pages * 8 as UsedKB, au.data_pages * 8 as DataKB
FROM sys.allocation_units AS au
JOIN sys.partitions AS p ON au.container_id = p.partition_id
JOIN sys.objects AS o ON p.object_id = o.object_id
--JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id
WHERE o.is_ms_shipped <> 1 and filegroup_name(au.data_space_id) = 'yourfgname'
ORDER BY o.name, p.index_id;
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 19, 2012 at 8:11 am
Thank you Perry for the script.
That Really Big Table that I moved to a new file group?
Well, I sort of moved it. The Primary Key Index and the Table went to the new file group,
But when I recreated the other 5 indexes, I failed to edit to the new filegroup.
So I have 46.2GB of index data in the old filegroup.
That explains why I can't go below 52GB on shrink.
As far as the EMPTYFILE goes, I don't believe that complicated anything. That just proved the point to myself that it had nothing to do with initial database file size.
This issue is resolved. Thanks again
March 19, 2012 at 9:28 am
sbaker-757360 (3/19/2012)
Thank you Perry for the script.
You're welcome
sbaker-757360 (3/19/2012)
But when I recreated the other 5 indexes, I failed to edit to the new filegroup.So I have 46.2GB of index data in the old filegroup.
That explains why I can't go below 52GB on shrink.
😉
Glad you got it resolved
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply