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

Reindex - database grow Expand / Collapse
Author
Message
Posted Monday, October 12, 2009 7:34 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:09 AM
Points: 761, Visits: 1,083
Hi,

When i reindex my databases they grow in size.
Casn someone explain way?

Thank you
Post #801564
Posted Monday, October 12, 2009 9:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:38 PM
Points: 31,368, Visits: 15,834
This is a logged operation, and so two copies of the index are needed until the reindexing is finished.

Your databases are growing because you have not properly sized them. The database should have enough free space for normal operation, including reindexing, as well as enough space for data growth for the next month at least. Maybe people ensure they have enough space for 3-6 months.

You want to manage your space, not expect autogrow to do it for you. Make the files large enough for operations, and grow them periodically, a few times a year.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #801651
Posted Monday, October 12, 2009 9:26 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:09 AM
Points: 761, Visits: 1,083
Thank's for reply.

I understand that.

But in my case, it is not the Log that grow, but the data file.

Why?

thank you


P.s - my database is in simple recovery model, that's why the log dont grow (i'm aware of the the danger in simple recovery model)

Post #801655
Posted Monday, October 12, 2009 9:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:38 PM
Points: 31,368, Visits: 15,834
Everything is still logged in simple mode. Once the transaction commits, the space is reused.

The index is rebuilt as a separate copy, that's in the data file, and you need the space for that to occur. You have two copies until the rebuild is finished.

Again, if the file grows, you are not keeping enough free space in there.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #801660
Posted Monday, October 12, 2009 9:36 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:09 AM
Points: 761, Visits: 1,083
I understud.

Thank you very much
Post #801662
Posted Tuesday, October 13, 2009 7:04 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:09 AM
Points: 761, Visits: 1,083
When the rebuild is finish in the data file , the cppy of the index is eliminated from the datafile?


Post #802114
Posted Tuesday, October 13, 2009 2:07 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
Yep.

Mj
Post #802382
Posted Tuesday, October 13, 2009 2:38 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,666, Visits: 5,316
But the space is not reclaimed. I wouldn't shrink the file as this is now giving a better picture of the space you truly are beginning to need - for maintenance jobs anyway.

-- You can't be late until you show up.
Post #802396
Posted Tuesday, October 13, 2009 2:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:38 PM
Points: 31,368, Visits: 15,834
Terry is correct. The space is available to be re-used by the next reindex job, or by data growth, but the file size does not shrink as far as the OS is concerned.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #802402
Posted Wednesday, October 14, 2009 1:54 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:09 AM
Points: 761, Visits: 1,083
Strange... very strange....
I know i should not make a shrink, because of all that was written in this post, but i have tried because i'm in the dev. enviroment and the space was not releaced to the OS.

Why? even with shrink it does not free the space, after a reindex?


Thank you.
Post #802552
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse