Post reply

Rebuild Script Has Been Running 24 Hours

  • March 7, 2014 at 6:20 am

    #290565

    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

  • March 7, 2014 at 6:31 am

    #1695090

    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

  • March 7, 2014 at 7:45 am

    #1695121

    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

  • March 7, 2014 at 8:27 am

    #1695137

    Run sp_who2 at a minimum?

  • March 7, 2014 at 8:52 am

    #1695146

    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

  • March 7, 2014 at 9:21 am

    #1695154

    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

  • March 7, 2014 at 9:31 am

    #1695158

    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 6 (of 6 total)

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