March 4, 2010 at 3:09 am
I moved some big tables from PRIMARY to SECONDARY filegroup. I did this using
1) DROP INDEX indexname ON tablename WITH (MOVE TO [SECONDARY])
2) CREATE CLUSTERED INDEX indexname ON tablename (fields) WITH (options) ON [SECONDARY]
some tables were moved using
1) CREATE CLUSTERED INDEX indexname ON tablename (fields) WITH (options, DROP_EXISTING = ON) ON [SECONDARY]
Management Studio shows that there is plenty of space in PRIMARY filegroup (Available free space is about 60% of All moved tables size). Unfortunately the space from the PRIMARY filegroup can not be released.
I tried shrink Files, also with "Reorganize pages before releasing unused space" - it doesn't release the space.
I tried to do this after the FULL backup - it does not release the space.
I tried to do this after Full and then Transaction Log backup - the space is not released
Does anyone have any ideas how to release the free space from the PRIMARY filegroup after moving tables to other filegroups?
March 5, 2010 at 12:20 am
what was the initial size when you create the data file?
if I am not mistaken, you are not able to shrink a data file below its initial size.
(if I am wrong, please correct me. thx)
March 5, 2010 at 12:02 pm
Are you getting your reserved versus used with something like the following and then doing a DBCC shrinkfile(1/*or whatever the number is*/,####in MB) ?
Declare @File_Stats table (
File_ID int
,File_Group_ID int
,Total_Extents int
,Used_Extents int
,Logical_File_Name varchar(500)
,Physical_File_Name Varchar(500)
)
insert into @File_Stats
exec ('DBCC ShowFileStats')
select
File_ID
,File_Group_ID
,Case
When Total_Extents * 64 < 1024 Then Cast(Total_Extents * 64 as Varchar(20)) + 'KB'
When Total_Extents * 64 / 1024 < 1024 Then Cast(Total_Extents * 64 / 1024 as Varchar(20)) + 'MB'
Else Cast(Total_Extents * 64 / 1024 /1024 as Varchar(20)) + 'GB'
End As Space_Reserved
,Case
When Used_Extents * 64 < 1024 Then Cast(Used_Extents * 64 as Varchar(20)) + 'KB'
When Used_Extents * 64 / 1024 < 1024 Then Cast(Used_Extents * 64 /1024 as Varchar(20)) + 'MB'
Else Cast(Used_Extents * 64 / 1024 / 1024 as Varchar(20)) + 'GB'
End As Space_Used
,Logical_File_Name
,Physical_File_Name
From @File_Stats
March 8, 2010 at 8:36 am
I think you're right, but the initial size of my database was small (about 15GB after upgrade from AX30), and I started to move some tables to other filegroups when the size was about 70GB (before that - using shrink file with page reorganize did the job - free space in db did fell very low - almost to zero %). Now I have secondary file about 29GB (only 9% of free space), tertiary file about 7GB(only 14% of free space), and the first (original file) should is 60GB (27% of free space). Whatever I do - I can not do these files to be closer to the size of data inside the file (I would like them to have 1 to 5% of free space, but not 27%).
Yes, I did try shrinking with "Reorganize pages before releasing unused space" with shrink file to 0MB (anyway it allows shrinking only to 42GB, as it is the size of data in the file). But it did not make the file smaller - it is still 60GB (and 27% of free space).
I have no more ideas where to look
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply