Ongoing Maintenance of Indexes: rebuild

Hi experts:

i am reading REBUILD, but i do not understand:
Performed as a single transaction with potential requirement for a large amount of transaction log space
Who is Participating?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, rebuild performs as a single transaction for each index and may require a large amount of transaction log space, depending on the size of the index.
It's recommended a transaction log backup immediately after finish the rebuild maintenance window.
A rebuild/reorganize should be an integral part of regular process.
Monthly or every other month.

There are several scripts (tsql that can be setup as SQL job)
Several use index fragmentation as a determinant whether to reorganize or rebuild the index one at a time which leads to a smaller transaction log requirement.

There is an example of such on a Microsoft and I believe on EE, but do not have it at my finger tips.
Brian CroweDatabase AdministratorCommented:
I strongly recommend using the Olla Halengren scripts for Index Maintenance.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HuaMin ChenSystem AnalystCommented:
You can remedy index fragmentation by reorganizing or rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index. Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.
Vitor MontalvãoMSSQL Senior EngineerCommented:
@Enrique_aeo. what you do not understand?
What's a Rebuild in SQL Server or when to use it?

Rebuild is used to remove fragmentation on indexes by dropping the index and recreate again. You should use it on your maintenance plans, depending on the fragmentation level of the indexes.
Don't use the SQL Server maintenance plan wizard for rebuilding index. The one from Olla's posted above is very good. It will only rebuild if the index fragmentation is higher than 30% otherwise will reorganize only. Less than 5% of fragmentation won't do nothing since the trade off is not worthwhile.
enrique_aeoAuthor Commented:
Thank experts

but i neee understand
Performed as a single transaction with potential requirement for a large amount of transaction log space
If you run dbcc rebuild index, it will be run as a single transaction for all indexes and will require a large amount of transaction log space. When run by scripts i.e. an index is rebuilt or reorganized, once the transaction of the individual index is completed and committed, the transaction log space used for the individual transaction is released.

Think of it you are given approximately 100 pages to count.
You can do it in one shot, counting from one till the end (and if you get distracted or loose your place, you will have to start counting from the beginning)
Or you can take the first 10 position them in one way, the next ten and place them perpendicular to the (along the height/along the weigth), repeat until you are then. then if the last one is less than 10, you will count the group of piles and add the last pile. This way you need only remember up to 10.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.