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

avatar image

narendba
658 34 44 54

(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

avatar image

Magnus Ahlkvist
21.1k 19 39 42

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

avatar image

Blackhawk-17
12k 30 35 42

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

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:

x2018

asked: Sep 18, 2012 at 09:01 PM

Seen: 448 times

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

Copyright 2016 Redgate Software. Privacy Policy