• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

alter Index in sql2005 std edition

Hi,
I'm using sql2005 std edition, so when I have following alter index statement, will it cause page lock during the operation ? If during the same time, we have an insert operation, will the insert operation must wait till page lock it release by the alter index operation ?

ALTER INDEX [ClusteredIndexTime] ON [dbo].[MsgLog] REORGANIZE WITH ( LOB_COMPACTION = ON )
0
motioneye
Asked:
motioneye
  • 2
3 Solutions
 
morguloCommented:
Yes, Alter index aquires locks. Try use alter index when it is the lowest database usage.
0
 
cyberkiwiCommented:
From 2005 onwards, you can rebuild using the ONLINE option

ALTER INDEX ALL ON TBL REBUILD WITH(ONLINE = ON)

ALTER INDEX SomeIndexName ON TBL REBUILD WITH(ONLINE = ON)

Open in new window


More info: http://www.techrepublic.com/blog/datacenter/rebuild-indexes-online-with-sql-server-2005/249
0
 
motioneyeAuthor Commented:
This online rebuild only available in enterprise or  it cover both std and enterprise edition ?
0
 
cyberkiwiCommented:
For Standard (just reread question) it is not a page lock - it will lock the entire table without the ONLINE option available.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now