June 2, 2016 at 6:26 pm
hello , I have a sql 2008 r2 SP3 enterprise production instance, I am planning to use OLLA's scripts for my index maintenance(https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html). I am planning to do my index rebuilds online but according to a senior DBA, the online rebuild operation uses TEMPDB to sort and it causes for it to grow a massive amount instead of the rebuild offline. Is this True? .. For what I read, rebuild online might cause the log of the database to grow but as long as you do a DB full backup after , it will be ok.
June 2, 2016 at 8:16 pm
The online operation keeps a temporary copy of information available as it does the rebuild. Temporary just about anything within SQL Server goes into tempdb. You can read about the guidelines for online operations here. You can read about the disk requirements for online operations here, and it absolutely does go to tempdb.
Now, whether or not this will be a "massive amount" of tempdb space completely depends on the amount of activity on your system. If you're doing index rebuilds at off-peak hours (something I would strongly recommend, even for online operations), then the amount of tempdb consumed is basically in line with the amount of activity on the system and the size of your indexes. Read about it in the second link.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2016 at 8:19 am
thank you Grant!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply