You need to analyze the wait chain. What is likely happening is that the OIB has completed the first two phases (perhaps long ago), and is now waiting for user queries to drain in order to do the final phase. One or more user queries is blocking OIB final phase, and as OIB has requested an SCH_M lock it places every other user queries in the wait list. The culprit is the user query blocking the final phase. Look at sys.dm_exec_request.blocking_session_id
but make sure to walk up the chain until you find the one spid that is blocking w/o being blocked in turn.
Both offline and online reorg/rebuild operations consume log space. This is true for ALL operations that modify a database in any way, even for minimally logged operations such as TRUNCATE TABLE
. This is how SQL Server maintains transactional consistency. "Consistency" is impossible unless every action is logged so it can be rolled back or forward if necessary.
Index reorganization uses less log space than an index rebuild. Reorganization defragments the index page-by-page, whereas a rebuild creates an entirely new copy of the index being rebuilt, then drops the old copy of the index. If an individual index is 1GB, rebuilding it will require at least 1GB, plus space for any rollback to take place that may happen as a result of the rebuild either failing or being cancelled. Since any rollback operation will also be logged, log space is reserved prior to the start of all transactions to ensure the transaction can be rolled back successfully. This indicates the maximum potential log space required for an index rebuild would be double the size of the index at the time the rebuild operation begins. So for a 1GB index, you should ensure you can support at least 2GB of log space in use by the rebuild operation for the duration of the operation. This is not a guarantee that much space will be used, it is simply the maximum the operation might need. Any other simultaneous transactions occurring will need additional space.
Any operations that consume tempdb also consume tempdb log space. If you enable SORT_IN_TEMPDB
, you need enough room in tempdb log to support sorting the entire index; this amount depends greatly on the structure of the index, including things like how large the key columns are.
If index reorg/rebuild operations are performed serially, that is one-after-another, then you'll need space in the transaction log for the largest index size x 2. i.e. if your largest index is 1GB, to be safe you need to ensure 2GB of transaction log is available for your index operation. If you have simple logging in effect at the time of the reorg/rebuild job, and all operations aren't in a single transaction, transaction log space will be re-used by each individual operation. If you have full transaction logging enabled, and no transaction log backups occur during the reorg/rebuild job, then the log needs to be size at the total size of all indexes to be rebuilt + the size of the largest index to be rebuilt. This can be mitigated somewhat by running log backup jobs during the rebuild process.
Best Answer
Consider what Paul Randal says in his post on Search Engine Q&A #19: Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking):
He reiterates that point in SQLskills SQL101: REBUILD vs. REORGANIZE:
Finally, the documentation also discusses this on the page Reorganize and rebuild indexes:
So the answer to your question is yes, a new index is created and the old one is dropped when rebuilding an index offline (although it will generally require less disk space than the online version of the operation).