SQL Server Performance

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

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by snivas, Oct 20, 2008.

  1. snivas Member

    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 index accupaing 40GB space in Tlog)

    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.
  2. madhuottapalam New Member

    When you create CI index on a Heap , it takes almost exact size of the table . Are these TL and Data files kept in the same drive?
    Madhu
  3. snivas Member

    Hi Madhu
    No,they are in Diff Drives.
    If I change SORT_IN_TEMPDB=ON will it help ?
  4. Saurabh Srivastava New Member

    *************************************************************************************************************************************************
    When you create CI index on a Heap , it takes almost exact size of the table . Are these TL and Data files kept in the same drive?
    Madhu
    Madhu K Nair
    http://madhuottapalam.blogspot.com/
    http://experiencing-sql-server-2008.blogspot.com/
    *************************************************************************************************************************************************
    I don't understand the highlighted part. Is it something you expereinced or read in any Microsoft article. If it is MS artcile please share with us as well.
    Srini,
    What is the size of table where you are creating these indexes?
    Are there any non-clustered indexes on those tables? If yes, how many and size for each of them
  5. MohammedU New Member

    I believe Madhu was referreing from BOL topic "Determining Index Disk Space Requirements "
  6. Saurabh Srivastava New Member

    Thanks Mohammed.
    Srinvas can apply recommendations from this article.

Share This Page