x

Is there any locked file is created internally while rebuild index running?

Is there any locked file is created internally while rebuild index running?
more ▼

asked Sep 18, 2012 at 09:01 PM in Default

narendba gravatar image

narendba
570 28 39 46

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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:

  • Disabled indexes
  • XML indexes
  • Indexes on local temp tables
  • Partitioned indexes
  • Clustered indexes if the underlying table contains LOB data types
  • Nonclustered indexes that are defined with LOB data type columns
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.
more ▼

answered Sep 19, 2012 at 05:51 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

thanks Magnus, while running rebuild at offline or online any file created physically or logically...
Sep 20, 2012 at 11:12 AM narendba
See the answer from @Greg regarding physical and logical files.
Sep 20, 2012 at 01:11 PM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Sep 20, 2012 at 12:19 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948

asked: Sep 18, 2012 at 09:01 PM

Seen: 405 times

Last Updated: Sep 20, 2012 at 01:11 PM