Is there any locked file is created internally while rebuild index running?
asked Sep 18, 2012 at 09:01 PM in Default
When you rebuild an index, you can specify whether the operation is performed ONLINE or not. The option to rebuild the index ONLINE is only available in Enterprise Edition of SQL Server 2005. There are limitations to when ONLINE rebuild can be performed. The option is not available for:
An OFFLINE rebuild will drop the existing index and rebuild it, while an ONLINE rebuild will create a new index, based on the old index, and then drop the old index. Therefore, during an OFFLINE rebuild, the index will not exist and can't be used. Also, during an OFFLINE index rebuild, SQL Server will hold a shared lock on the underlying table. Therefore, changes to the data (insert, update, delete) is not available during the index rebuild.
answered Sep 19, 2012 at 05:51 AM
There are no new files created.
Your data and log files for the database being worked on, as well as those of temp db, will be in use to perform the changes.
By the nature of SQL Server these files are always locked open by the service as repeated file open/close operations would be too expensive an operation for an RDBMS to operate effectively.
answered Sep 20, 2012 at 12:19 PM