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.