Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Guidance with shrink and rebuild/reorganise indexes Expand / Collapse
Author
Message
Posted Monday, July 29, 2013 1:56 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 7:12 AM
Points: 31, Visits: 149
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.
Post #1478460
Posted Monday, July 29, 2013 2:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
SORT_IN_TEMPDB is it set or not, i hope the large index sorting keeps it growing.

Regards
Durai Nagarajan
Post #1478474
Posted Monday, July 29, 2013 2:32 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 7:12 AM
Points: 31, Visits: 149
No, I'm pretty sure that option isn't turned on.

Cheers.


Keep the rubber side down and the shiny side up.
Post #1478476
Posted Monday, July 29, 2013 2:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
then possible sorting is happening on the same DB and possible for DB growth

Regards
Durai Nagarajan
Post #1478479
Posted Monday, July 29, 2013 3:04 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 7:12 AM
Points: 31, Visits: 149
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 )


Keep the rubber side down and the shiny side up.
Post #1478489
Posted Monday, July 29, 2013 3:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1478498
Posted Monday, July 29, 2013 3:36 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 7:12 AM
Points: 31, Visits: 149
Many thanks Durai, you've been most helpful.



Keep the rubber side down and the shiny side up.
Post #1478502
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse