Rebuild Script Has Been Running 24 Hours

  • ppierce10

    SSC Journeyman

    Points: 97

    I am using SQL Express 2005. A database has reached its maximum size and needs to be shrunk. Prior to shrinking I checked the database and found several error in the index. So I did a backed up and ran the following script to repair and rebuild

    alter database sbsmonitoring set SINGLE_USER

    DBCC CHECKDB (sbsmonitoring, repair_rebuild)

    alter database sbsmonitoring set MULTI_USER

    The script has been running for 24 hours.

    Is it normal to take this long to repair and rebuild a 4 gig database?

    At what point do I take some action and what steps would I take to correct the situation

    Thanks

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Check if it's blocked and if so by what and on what resource.

    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
  • ppierce10

    SSC Journeyman

    Points: 97

    Thanks for your quick response, but I am not sure how to check if it is blocked, could you please explain how I would do that?

    Perry

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Run sp_who2 at a minimum?

  • SQLRNNR

    SSC Guru

    Points: 281210

    Most likely the alter database is waiting infinitely to complete and thus holding up your checkdb.

    You should try the alter database as follows

    alter database blah

    set single_user WITH ROLLBACK IMMEDIATE;

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • ppierce10

    SSC Journeyman

    Points: 97

    Thanks, I did not see any blocks. I checked and found the process had been suspended. Killed the process and was able to correct the situation by renaming my data file and log file to .bak. Then restored the database to a backup I had made when the database was created so I now have a new clean database.

    Thanks for your help

  • SQLRNNR

    SSC Guru

    Points: 281210

    Good to hear it is resolved.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 7 posts - 1 through 7 (of 7 total)

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