November 16, 2011 at 2:36 am
Hi,
We have one huge table around 1TB size. We are going the delete some old data from that table aroung 1/4th of data. Do you recommend to shrink the database after deleting the data?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
November 16, 2011 at 2:47 am
Are you under any space constraints on the hard drive? If not then I wouldnt shrink the database.
Shrinking will cause fragmentation in the database, so you will have to rebuild the indexes on any objects within the file you have shrunk, which will mean that the file will grow again to do the rebuilds so kind of defeating the object.
How soon will it take the table to have the same amount of data back into the table, so your deleting approx 250GB of data, how fast will 250GB of new data be pushed into the table?
If you are stuck for space, I would take a copy, but it on a seperate server, delete, shrink, rebuild and see how it goes.
One other thing is, are you running Standard or Enterprise, as you might have to do offline index rebuilds if your on standard.
Personally I would only shrink if there was hard drisk drive capacity issues and it was going to cause a massive production issue as you cannot get more space for the server in time before the drive fills up.
November 16, 2011 at 2:49 am
If you need the disk space, yes.
Be careful: shrinking the database fragments indexes badly, you should rebuild your indexes after that.
If you don't need the disk space, leave it this way: it will likely grow again.
-- Gianluca Sartori
November 16, 2011 at 2:59 am
How big's the DB? How long do you expect it will take to reuse that 250GB (based on the database's current growth rate)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2011 at 3:02 am
The database size is 1.2TB. And growth rate is around 2GB per day. So around 120 days to fill the space.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
November 16, 2011 at 3:06 am
In that case, I'd say don't shrink. Just leave the free space in the data file for the DB to reuse.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2011 at 5:19 am
Thanks Gail.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply