July 27, 2010 at 2:50 pm
When I DEFRAG (rebuild/reorg) Indexes my Database files (mdf/ldf) grow. But if I SHRINK my database, that will cause fragmentation.
Will the space added to my MDF/LDF files (they grow) when I do my Index rebuild/reorg be used later on (for future index rebuilds) or is that space wasted because its fragmented, too?
What's the solution?
...thanks
July 27, 2010 at 2:54 pm
You could leave the space in the files. The space will be used later on. Empty space inside the data file is not fragmented within the file (the filesystem is a different story).
If you leave the space there, the data will eventually fill the file and use up the free space. If you run a defrag operation, then they space would be used by the operation as necessary.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 27, 2010 at 5:44 pm
Also note that the log file growth will be truncate during your next full or transaction log backup thereby allowing you to reuse the space for the next rebuild or other things.
Fraggle.
July 27, 2010 at 5:52 pm
It's not wasted space. You cannot run a Windows installation with 0% free space. Try it and you'll see. Things don't work. The same applies to SQL Server. There needs to be a pad, a buffer, for maintenance operations as well as data growth.
Don't think of this as wasted space. It's space needed for the server to function. Plan for it and allocate it accordingly.
July 28, 2010 at 10:57 am
Thanks to all for your replies!
July 28, 2010 at 11:15 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 28, 2010 at 8:20 pm
While I absolutely agree with the others that you need free space in your data files to perform maintenance, you may be able to get by by disabling your non-clustered indexes prior to building them offline. When you disable a non-clustered index, all the pages are released, so you're rebuilding them in place. This will cut down on the amount of data file space required, but you'll still need plenty of space in the transaction log.
If you reorganise, you don't require as much space in the data file to perform the maintenance, as the reorg uses a single page to perform the work. Again, log space is what's needed, and if the table is severely fragmented, a reorg can suck up more log space than just rebuilding it (and the rebuild is a lot cleaner).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply