We help IT Professionals succeed at work.

Release space from primary file group

Hello,

I've one old database (SQL Server 2005) with 30 GB .mdf space.
I wanted to move one table in another file group so, I did that after creating clustered index. That tables contains 19 GB space and it is transferred in different drive and file group.

But primary file, still contains 30 GB space. I want to release that unused space from primary file group.

About Fill Factor, I did check it contains 0%.

Can you please assist me on it?

Best Regards,
Mohit Pandit
Comment
Watch Question

Commented:
you need to shrink the filegroup.

http://msdn.microsoft.com/en-us/library/ms189493.aspx

Commented:
SQL uses filegroups as a virtual concept - maintenance (e.g. manipulating space) is typically perfomed on the individual file either the main data file (*.MDF or one of possible several secondary data base filles (*.NDF) or the log file (.LDF)
I am assuming you may feel more comfortable working i the gui environment of SQL management studio. Once you have selected the database goto Task then Shrink then Files. Here you would select the *.mdf file - you can first use the option to release unused space (back to the OS) This option will not always release all the space back - if not you can follow up with the next option which will allow  you to specify the size you want to shrink the file to. My recommendatiion is to leave at least 20% free filespace in the file.

Author

Commented:
Hi Skarai,

Can you please look attached screen shot here and give your suggestion as aforesaid?



Best Regards,
Mohit Pandit
Snap-Primary.PNG

Author

Commented:
*
1. As per screen shot, our primary data file size is 109 GB (112521.75 MB) around.
2. Available free space is 43 GB (45009.81 MB).

So, as per your recommendation, we can shrink file up to 21 GB of currently allocated space. Right?

Can you please confirm again before I shrink?
Commented:
Basically 10% free space is enough when your db size is more than 10 GB. Your used database file  size is 66 GB. So, you can shrink all the space leaving 75 GB in the mdf file.
You can shrink your database file to 76800 MB.
Commented:
Your overall files size is ~ 112 GB of which 40% are free meaning ~67 GB of the file space are actually occupied by data - thus the information you see in Minimum which tells you what you can shrink the file to - the smallest possible file size will be 67512 MB. An experienced DBA will probably not go this low, unless you already have another file ready to go and are willing to stop this file from growing again. I personally prefer not to have individual files grow larger than 100 GB for midsize databases. Btw the shrink operation may run for several hours - it may be advisable to take this in steps - lets say 100000 MB as first step then 80000 as a second shrink.

Author

Commented:
Ok, thank you very much for response.

But, can you please give your feedback on below scenario?

--> I change 100000 MB as first step
--> and it is executed successfully
--> after that I'll change 80000 MB in second shrink
--> now, 2nd time, it does fail.

Then, what will be happen in that case as I am not very much experienced on that?

Commented:
Try e.g. 95000 MB next and proceed with e.g. 5000 MB increments down to the target size. Best to try this when the DB and server are fairly idle (during off hours) It is not unusual that this requires several tries.

Author

Commented:
Thanks!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.