December 28, 2007 at 5:25 am
I need to create an index on a large table. There is a 24 our service writing to the table. When I try to create the index the table is locked by the processes and my service stopps. I need to find out if anyone has a trick to create the index without causing all the locks.
Thanks
December 28, 2007 at 5:29 am
You could try and use the online option when creating the index... taken from BOL
ONLINE = { ON | OFF }
Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.
Gethyn Elliswww.gethynellis.com
December 28, 2007 at 5:37 am
Thanks will try it
December 28, 2007 at 6:14 am
Online indexing is only available in the Enterprise Edition of SQL 2005.
An alternative would be to copy your table, create the index on the copy, and then do some renaming and application of changes that happened during the indexing process. It could be complicated, but it would reduce down time to the amount of time it takes to rename a table (miliseconds).
December 28, 2007 at 10:55 am
Michael has a good point here. You will have to do some pretty complex maneuvering to get this accomplished, fortunately SQL Server 2005 has a generator to create all the steps.
So first create your index in Design mode of SSMS --> Click on the "Generate Change Script" button on the Table Designer Tool bar. You can then cancel the changes (without saving) and run the script at your leisure, preferably a time when there is minimal activity.
December 28, 2007 at 10:59 am
Forgot to mention that you have to change a column defintion. You will not save the change, so it doesnt matter. This is to trick SSMS into creating the rest of the script for you.
The easiest way is to change a char or varchar to 1 number higher.
Recap.
Create your index --> change a char or varchar to 1 number higher --> generate the script.
**Check the script before running and make any necessary modifications**
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply