Shrink HUGE data file

Question

Shrink HUGE data file

WhiteLotus

SSChampion

Points: 12122
More actions
January 24, 2017 at 6:01 pm

#325962

Hi All,
Last nite I did purging in a big table ( 1 billion records ) it was successful but the problem is I couldn’t shrink the data file ( even tho it has 95% available free space)
Database file size is 280GB
In GUI , When I chose “ Reorganize pages before releasing unused space , and set Shrink file to : 260GB
It says : Could not adjust the space allocation
At that time full back up was also running ( automatic schedule every nite )
Could it be due to full back up?
When I chose set shrink file to 150 GB, it was processing but after a few hours, it failed ( the error was something like this “ transaction is rollback due to locking ..”)
What I should I do to reduce the DATA FILE size now ?
I really need a space ..
Any feedback is much appreciated
Many thanks
January 24, 2017 at 9:47 pm

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

WhiteLotus - Tuesday, January 24, 2017 6:01 PM
Hi All,
Last nite I did purging in a big table ( 1 billion records ) it was successful but the problem is I couldnâ€™t shrink the data file ( even tho it has 95% available free space)
Database file size is 280GB
In GUI , When I chose â€œ Reorganize pages before releasing unused space , and set Shrink file to : 260GB
It says : Could not adjust the space allocation
At that time full back up was also running ( automatic schedule every nite )
Could it be due to full back up?
When I chose set shrink file to 150 GB, it was processing but after a few hours, it failed ( the error was something like this â€œ transaction is rollback due to locking ..â€)
What I should I do to reduce the DATA FILE size now ?
I really need a space ..
Any feedback is much appreciated
Many thanks

How much space do you have and how big is the data? What is your maintenance window?
😎

Sergiy SSC Guru Points: 110208 More actions · Answer 2

WhiteLotus - Tuesday, January 24, 2017 6:01 PM

Hi All,
Last nite I did purging in a big table ( 1 billion records ) it was successful but the problem is I couldnâ€™t shrink the data file ( even tho it has 95% available free space)
Database file size is 280GB
In GUI , When I chose â€œ Reorganize pages before releasing unused space , and set Shrink file to : 260GB
It says : Could not adjust the space allocation

Does this table have a clustered index?

_____________
Code for TallyGenerator

WhiteLotus SSChampion Points: 12122 More actions · Answer 3

Hi All ,

I sorted this out by executing this statement :
DBCC SHRINKFILE (1, TRUNCATEONLY);

cheers