Post reply

I try to create one index but it went to suspended mode need help to run this query successfully

  • January 22, 2014 at 7:34 am

    #287543

    hi All,

    i try to create one index but it went to suspended mode.

    please help me to run this query successfully.

    Details as follows:

    IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_PDValue_PDDataMarketSignal_tbl')

    CREATE NONCLUSTERED INDEX IX_PDValue_PDDataMarketSignal_tbl

    ON PDData.GlobalPD.PDDataMarketSignal_tbl(PDValue)

    GO

    suspended details:

    insert [GlobalPD].[PDDataMarketSignal_tbl] select * from [GlobalPD].[PDDataMarketSignal_tbl]120suspendedCREATE INDEX621939158

    data in that table :

    sp_spaceused [GlobalPD.PDDataMarketSignal_tbl]

    PDDataMarketSignal_tbl1060763508 109236584 KB28862816 KB80449224 KB0 KB

    please help to how to resolve this issue.

    Thanks

    Bhanu

  • January 22, 2014 at 7:39 am

    #1682406

    Suspended just means it's waiting for something, probably a lock or IO. Be patient.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 22, 2014 at 11:33 am

    #1682535

    If you can use online rebuild, it might be worth cancelling the current index create and running an online version.

    Also, you should consider specifying other options -- or not -- such as FILLFACTOR and SORT_IN_TEMPDB before finally running the create index command.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • January 23, 2014 at 6:24 am

    #1682746

    Keep in mind while the index build is running queries against that data will be stopped... or vice versa as well.

    The bigger the table the longer it will take to build the index.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply