none
Creating Cluster Index on table --Hiting Tlog space....Create not happing ..Help me urgent

    Question

  • Hi all

     

    Yester day I run the script for creating cluster index script...it is fail saying Tlog full.Please help how to come out from the problem

     

    here the script.(creating accupaing 40GB space in Tlog)

     

    Code Snippet

    CREATE CLUSTERED INDEX [C_Table1] ON [dbo].[Table1]
    (
     [column_ID] ASC
    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, FILLFACTOR=90) ON [PRIMARY]

    ########################################################

    CREATE CLUSTERED INDEX [C_Table2] ON [dbo].[Table2]
    (
     [column_ID] ASC
    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, FILLFACTOR=90) ON [PRIMARY]


    #########################################################

    CREATE CLUSTERED INDEX [C_table3] ON [dbo].[Table3]
    (
     [column_ID] ASC
    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, FILLFACTOR=90) ON [PRIMARY] 

     

     

    Thanks in Advance.
    Tuesday, October 21, 2008 4:25 AM

Answers

  • Your log file is full and hence as a result the index creation failed. You wouldn't be able to perform anything in the database until you truncate the log file. If the database is in Full/Bulk logged recovery model take a transaction log backup to truncate the log file else in simple recovery model use backup log with truncate_only command to do it..Refer this link for more info,
    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Log%20file%20growth%20in%20SQL%20Server&referringTitle=Home

    - Deepak
    Tuesday, October 21, 2008 4:49 AM
    Moderator
  • What is the size of the tables? It requires almost the same size to create CI. Sorting in temp db will not help much. 

     

    From BOL

    Clustered Indexes

    Creating a clustered index on a table (heap) or dropping and re-creating an existing clustered index requires additional workspace to be available in the database to accommodate data sorting and a temporary copy of the original table or existing clustered index data. For more information, see Determining Index Disk Space Requirements. For more information about clustered indexes, see Creating Clustered Indexes.

     

    Whenever an index is created, rebuilt, or dropped, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits. Additional temporary disk space for sorting operations may also be needed. For more information, see Disk Space Requirements for Index DDL Operations.

    In this example, disk space requirements to create a clustered index are determined.

    Assume the following conditions are true before creating the clustered index:

    • The existing table (heap) contains 1 million rows. Each row is 200 bytes long.

    • Nonclustered index A contains 1 million rows. Each row is 50 bytes long.

    • Nonclustered index B contains 1 million rows. Each row is 80 bytes long.

    • The index create memory option is set to 2 MB.

    • A fill factor value of 80 is used for all existing and new indexes. This means the pages are 80 percent full.

     

     

    Madhu

    Tuesday, October 21, 2008 7:43 AM
    Moderator

All replies

  • Your log file is full and hence as a result the index creation failed. You wouldn't be able to perform anything in the database until you truncate the log file. If the database is in Full/Bulk logged recovery model take a transaction log backup to truncate the log file else in simple recovery model use backup log with truncate_only command to do it..Refer this link for more info,
    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Log%20file%20growth%20in%20SQL%20Server&referringTitle=Home

    - Deepak
    Tuesday, October 21, 2008 4:49 AM
    Moderator
  • Hi Deepak

     

    Thanks for replay if chaged

    SORT_IN_TEMPDB=ON in statement what will be the result

     

    Tuesday, October 21, 2008 5:02 AM
  • By setting that option ON, the sorting of key columns are done in Tempdb i guess. The default option is OFF as a result it is limited to the database in which the object resides..It might be successful if the tempdb has enough space. I haven't encountered a situation yet..

    - Deepak
    Tuesday, October 21, 2008 5:09 AM
    Moderator
  • What is the size of the tables? It requires almost the same size to create CI. Sorting in temp db will not help much. 

     

    From BOL

    Clustered Indexes

    Creating a clustered index on a table (heap) or dropping and re-creating an existing clustered index requires additional workspace to be available in the database to accommodate data sorting and a temporary copy of the original table or existing clustered index data. For more information, see Determining Index Disk Space Requirements. For more information about clustered indexes, see Creating Clustered Indexes.

     

    Whenever an index is created, rebuilt, or dropped, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits. Additional temporary disk space for sorting operations may also be needed. For more information, see Disk Space Requirements for Index DDL Operations.

    In this example, disk space requirements to create a clustered index are determined.

    Assume the following conditions are true before creating the clustered index:

    • The existing table (heap) contains 1 million rows. Each row is 200 bytes long.

    • Nonclustered index A contains 1 million rows. Each row is 50 bytes long.

    • Nonclustered index B contains 1 million rows. Each row is 80 bytes long.

    • The index create memory option is set to 2 MB.

    • A fill factor value of 80 is used for all existing and new indexes. This means the pages are 80 percent full.

     

     

    Madhu

    Tuesday, October 21, 2008 7:43 AM
    Moderator
  • Do you have unused disk space in excess of the 40 gigs?  Have you set a maximum size for your tlogs?  You could temporarily increase or remove the maximum, if that is the problem.

     

    Tuesday, October 21, 2008 5:09 PM
    Moderator
  • Hi SNivas
                  Setting SORT_IN_TEMPDB=On will only helps if your TempDB is located on different physical disk than your database. Else turning that option on is useless. 
    Cheers,
    Bhupendra
    Friday, October 24, 2008 11:32 PM