Forum Replies Created

Viewing 15 posts - 19,276 through 19,290 (of 49,552 total)

  • RE: Create Nonclustered Index hangs and won't complete

    Stefan Krzywicki (4/7/2012)

    Perry Whittle (4/7/2012)

    Unfortunately I can't supply you a query as I'm not at my computer. As Gail said kill the current session id for the create index and...

    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

    April 7, 2012 at 3:54 pm

    #1470481

  • RE: Create Nonclustered Index hangs and won't complete

    Ok, stop the create index, try running it again with maxdop 1 specified.

    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

    April 7, 2012 at 3:48 pm

    #1470477

  • RE: Create Nonclustered Index hangs and won't complete

    Stefan Krzywicki (4/7/2012)

    GilaMonster (4/7/2012)

    Blocking session of 0 means it's not blocked by another session

    What's the wait type?

    CXPACKET

    That's not the real wait type.

    Only one row in sys.dm_exec_requests? If so check sys.dm_os_waiting_tasks...

    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

    April 7, 2012 at 3:33 pm

    #1470470

  • RE: Create Nonclustered Index hangs and won't complete

    Blocking session of 0 means it's not blocked by another session

    What's the wait type?

    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

    April 7, 2012 at 3:24 pm

    #1470467

  • RE: Create Nonclustered Index hangs and won't complete

    I would be very surprised if a single drive can handle the IO loads you're talking about.

    Have you checked the windows system and application logs to make sure there are...

    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

    April 7, 2012 at 1:14 pm

    #1470443

  • RE: Create Nonclustered Index hangs and won't complete

    Stefan Krzywicki (4/7/2012)

    Is there a way to access this information now or would I have had to be capturing it all along. All I have is historical run times.

    You would...

    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

    April 7, 2012 at 12:56 pm

    #1470434

  • RE: Create Nonclustered Index hangs and won't complete

    Single drive, no RAID, no redundancy?

    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

    April 7, 2012 at 12:54 pm

    #1470433

  • RE: Create Nonclustered Index hangs and won't complete

    Stefan Krzywicki (4/7/2012)

    GilaMonster (4/7/2012)

    So H is getting hit by the index read, the sort space and the new index? Ow. Could be that you've just surpassed what the underlying IO...

    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

    April 7, 2012 at 12:48 pm

    #1470428

  • RE: Create Nonclustered Index hangs and won't complete

    So H is getting hit by the index read, the sort space and the new index? Ow. Could be that you've just surpassed what the underlying IO subsystem can handle.

    Got...

    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

    April 7, 2012 at 12:37 pm

    #1470422

  • RE: Create Nonclustered Index hangs and won't complete

    Stefan Krzywicki (4/7/2012)

    GilaMonster (4/7/2012)

    Snapshot isolation?

    The warnings point to something slowing down the IOs on teh TempDB drive. Check that there hasn't been any changes to the Io subsystem recently

    What do...

    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

    April 7, 2012 at 11:42 am

    #1470415

  • RE: Slect DB name , Recovery Model & last back up type

    Sysdatabases is deprecated, should not be used, included only for backward compatibility with SQL 2000, will be removed in a future version.

    SELECT name, recovery_model_desc, status_desc from sys.databases

    For log backups...

    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

    April 7, 2012 at 11:29 am

    #1470410

  • RE: Create Nonclustered Index hangs and won't complete

    Snapshot isolation?

    The warnings point to something slowing down the IOs on teh TempDB drive. Check that there hasn't been any changes to the Io subsystem recently

    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

    April 7, 2012 at 11:22 am

    #1470406

  • RE: Trouble using GO or other batch command.

    You're welcome.

    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

    April 6, 2012 at 4:58 pm

    #1470339

  • RE: Database-->Task-->Shrink-->Log-->Free Unused space; Recommended ?

    You shouldn't be shrinking the log at all.

    If the log is regularly reaching a certain size, then it needs to be that size for regular database activity. If you keep...

    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

    April 6, 2012 at 3:08 pm

    #1470296

  • RE: Trouble using GO or other batch command.

    I think I can tell you what's wrong....

    The table you're pulling the TOP (1) columns from is a different table to the one you're updating. So yes, it'll just be...

    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

    April 6, 2012 at 2:57 pm

    #1470293

Viewing 15 posts - 19,276 through 19,290 (of 49,552 total)