July 29, 2013 at 1:56 am
Hi,
I'm not really very offay with database shrinking per se. I know how to do it but there are some things that I'm not understanding so I'm after some explanation as to what's going on behind the scenes as to why I'm seing what I'm seeing.
The server and DBs I'm working on are all development. Shrink is evil and the code was written by Satan himself, I know, and I would never attempt any of this in a production environment.
First of all a bit of background information: The server I'm working on hosts several dev databases, one of them was huge, 75GB data file with one table containing half a billion rows. This was causing the server to run out of disc space so some action was required. I've managed to get this down to about 100 million by keeping only the last 14 days worth of records. All the DBs are also using Simple recovery.
After reducing the size of this particular DB I shrunk it to about 15GB and set up a job to delete any records older than 14 days to prevent it from getting rediculously large again. Obviously this has caused massive fragmentation (not that it matters particularly on this DB) so as an opportunity to learn I decided to rebuild the indexes.
From what I've read online, rebuilding should compact the pages and reclaim disc space. However, as I was running the rebuild (using a task in SSIS) I noticed the data file was actually growing in size.
So my question (after going round the houses a bit) is, what's goin on here? Why is the DB growing while the indexes are rebuilding? Will the DB grow in size permanently or is this just a temporary growth state while indexes are dropped and recreated?
I've read a number of articles about it but none of them seem to acknowledge this so I'm left a little confused.
Thanks in advance.
Keep the rubber side down and the shiny side up.
July 29, 2013 at 2:29 am
SORT_IN_TEMPDB is it set or not, i hope the large index sorting keeps it growing.
Regards
Durai Nagarajan
July 29, 2013 at 2:32 am
No, I'm pretty sure that option isn't turned on.
Cheers.
Keep the rubber side down and the shiny side up.
July 29, 2013 at 2:36 am
then possible sorting is happening on the same DB and possible for DB growth
Regards
Durai Nagarajan
July 29, 2013 at 3:04 am
OK, thank you.
That makes sense now. Will that growth be permanent after the rebuild has completed or will the database reduce in size again once the rebuild has completed?
I assume then from your reply that turning the sort results in tempdb on will stop this happening? Will that then cause the tempdb to permanently increase in size?
Sorry for all the questions, this is all new stuff to me so the thirst for knowledge is quite high (at the moment :-D)
Keep the rubber side down and the shiny side up.
July 29, 2013 at 3:33 am
Depends on new index size and DB will grow
User DB will never return it to OS automatically and tempdb will not return it till the next SQL restart.
Regards
Durai Nagarajan
July 29, 2013 at 3:36 am
Many thanks Durai, you've been most helpful.
Keep the rubber side down and the shiny side up.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply