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
enrique_aeoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
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.
0
Brian CroweDatabase AdministratorCommented:
I strongly recommend using the Olla Halengren scripts for Index Maintenance.

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
0
HuaMin ChenProblem resolverCommented:
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
0
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
0
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arnoldCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.