May 30, 2007 at 4:30 am
Hi
We have a DB which is 212gb in size (160gb with 52gb free space).We have a weekly maintenance plan which has 'reorganize data and index pages' set change free space per page % to 10%.
What I cannot understand is why the db 212gb and not somthething like 180gb. I have tried shrinking the file down before the maintenance plan runs but it just grows it back up again. I dont mind it being this big but I would just like to understand why.
Many thanks
Dave
May 30, 2007 at 5:55 am
It could be like during your maintenance task (reindex might cause your data file to grow during the reindexing, but it will not shrink the datafile but will show as free space), it requires more space in the database (data and log file) and it grows as much as it required and after the maintenance it shows it as free space
May 30, 2007 at 10:28 am
Reindexing causes a large growth in the data/log files. If you have to shrink the files, do so AFTER the reindexing is done.
-SQLBill
May 31, 2007 at 4:13 am
I agree with 50% of what SQLBill says on this.
If you use a maintenance plan to reindex, you will see a large amount of free space at the end of it all. A reindex builds a new copy of the index, and when that is complete it releases the old copy of the index as free space. A maint plan reindexes multiple tables in parallel, so you often see large amounts of free space when it finishes. If you write your own reindex process that only reindexes 1 table at a time, you will only need enough free space to reindex your largest table. Send me a private note for a copy of our proc to do this if it interests you.
I think the advice about shrinking is not good. There are a number of posts detailing how shrinking a reindexed database will undo most of the benefits of the reindex.
Consider... a reindex will leave your indexes using ascending series of extents, which maximises performance. The extents for 1 table may be interleaved with extents for other tables, but this is not significant for performance. A shrink will start at the highest-used extent and move it to the lowest available free extent. It will then move the next highest extent, regardless of the table to which it belongs, to the lowest available free extent. At the end of this process, the extents for your indexes are likely to be scattered throughout the database file. Therefore the performance gains of the reindex will be lost.
Additionally, repeated growth and shrink of database files will result in NTFS fragmentation, which will definitely harm performance and can only be cured by a Windows disk defrag. You will also experience poor database performance while the shrink is running, and again later when your database grows again. My advice is do not shrink any database file unless you expect the new size to remain the same for at least 3 months.
If you use a maintenance plan to reindex your database, then you need to allow for the free space needed by this process in your capacity planning process.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply