How to release the unallocated sapce in database???

Question

How to release the unallocated sapce in database???

Lavanyasri

SSCarpal Tunnel

Points: 4907
More actions
June 21, 2012 at 7:13 am

#258350

HI,
One week back i ran maintenance plan with rebuild index task .In the options i mentioned change free space per page percentage to 40% . After successful run of the job , i observed the database occupied more database space compare to previous . Its almost double of the previous size. By using the this query i found that
use databse name
sp_spaceused
unallocated space is more than the reserved space. I am facing some space issues at the restoration . Whe we take the backup , the backup size is around 12 GB , but we try to restore in other server its taking around 50 GB sapce.
Can you please advise how to release the unallocated space .
Thanks
Lavanya
June 28, 2018 at 11:23 pm

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 1

What was the fill-factor before you stared? I'm guessing 90%.

Youu could try (on a seperate server with a database copy), rerunning your maint plan but changing the per-page fill-factor to 90%

HTH

Adam Zacks-------------------------------------------Be Nice, Or Leave

durai nagarajan SSCoach Points: 15778 More actions · Answer 2

Schadenfreude-Mei (6/21/2012)
What was the fill-factor before you stared? I'm guessing 90%.
Youu could try (on a seperate server with a database copy), rerunning your maint plan but changing the per-page fill-factor to 90%
HTH

adam 80 % will be good right?

Lavanya can we know why you changed it to 40%.

Regards
Durai Nagarajan

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 3

durai nagarajan (6/21/2012)
Schadenfreude-Mei (6/21/2012)
What was the fill-factor before you stared? I'm guessing 90%.
Youu could try (on a seperate server with a database copy), rerunning your maint plan but changing the per-page fill-factor to 90%
HTH
adam 80 % will be good right?
Lavanya can we know why you changed it to 40%.

80% is fine, I said 90% as the OP stated that the db size had doubled 90%->40% = 50.

80% will sure save you some space but you might find it is still bigger then the original size.

Adam Zacks-------------------------------------------Be Nice, Or Leave

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 4

HI ,

Thanks for the update .

i run the maintenance plan again with fill factor 90 % , but i did not find any changes in the database size. The unallocated space still with same size.

Thanks

Lavanya

durai nagarajan SSCoach Points: 15778 More actions · Answer 5

Lavanyasri (6/21/2012)
HI ,
Thanks for the update .
i run the maintenance plan again with fill factor 90 % , but i did not find any changes in the database size. The unallocated space still with same size.
Thanks
Lavanya

SQL wont release the unallocated space to windows , do shrink db but not recommended.

Regards
Durai Nagarajan

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 6

I think u r not understanding what i am asking . I am not asking amount the memory [RAM], i want to release the Database unallocated space .

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 7

Lavanyasri (6/21/2012)
I think u r not understanding what i am asking . I am not asking amount the memory [RAM], i want to release the Database unallocated space .

What Durai is saying is that after you rebuild with 80-90% fill factor, you physical files (mdf,ndf's) wont shrink (as they have already extended).

You will have to shrink the files.

Adam Zacks-------------------------------------------Be Nice, Or Leave

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 8

Lavanyasri (6/21/2012)
HI ,
Thanks for the update .
i run the maintenance plan again with fill factor 90 % , but i did not find any changes in the database size. The unallocated space still with same size.
Thanks
Lavanya

Shrink the database files.

USE [YOUR_DB]
GO
DBCC SHRINKFILE (N'YOUR_DB' , {SIZE_IN_MB})
GO

Adam Zacks-------------------------------------------Be Nice, Or Leave

RedDoorSQL Ten Centuries Points: 1294 More actions · Answer 9

Do not shrink the database shrink the file,

if it doesn't release the space query the sys.databases catalog view and check the log_reuse_wait_desc column

If it says nothing it should of released the space otherwise if it says log_backup backup the log file and then re-try the shrink it should then work.