Database stuck in ''loading''

  • I have a sql2000 database that is stuck in loading.  The database was being restored from a replicated production database.  The restore failed due to a corrupt log.  I need to delete the database, but it will not delete.  The error message that I get is 'Database is being replicated, can not delete'.  I can not find where it is being replicated.  I have tried rebooting, it is still stuck.

     

    Thanks in advance.

     

    Dorothy

     

  • I don't know if this will work with a database that is being restored by replication, but...

    when a database is restored using WITH NORECOVERY, it will be stuck in LOADING mode until it gets the WITH RECOVERY command.

    Try running this via Query Analyzer:

    RESTORE DATABASE dbname

    WITH RECOVERY

    Replace dbname with the name of your database.

    -SQLBill

    Fixed typo. Changed WITH RESTORE to the proper WITH RECOVERY. Thanks to Shawn for noticing it.

  • I agree with SQLBill, but a bit of a typo...

    RESTORE DATABASE dbname WITH RECOVERY

  • Thanks Bill & Shawn.  It worked.

     

    -- Dorothy

     

  • I realize this thread is way old, but for the sake of documentation I came across this same problem and when looking for a solution it would've been nice to know. Anywho, just doing the RESTORE with RECOVERY didn't do anything for my situation. I had to do the RESTORE by moving the mdf and ldf to a larger drive b/c I had a space issue keeping me from completing a RESTORE in EM. Here is what finally worked via QA...

    RESTORE DATABASE db_name

    FROM DISK = 'F:\Blah Refresh\blah_blah\yadda\yaddayadda_db.BAK'

    WITH MOVE 'logical_name_of_datafile' TO 'F:\blah_blah.mdf',

    MOVE 'logical_name_of_logfile' TO 'F:\blah_blah.ldf'

  • I had the same issue as Ash73. As soon as I expanded my data drives to give them breathing room the database checkpointed and came online.

    I didn't have to stop the restore. As soon as I expanded the drive online, the restore finished.

    The way I could tell the restore was "finished" without coming online was due to the entry in SQL Server Log:

    Message

    Starting up database 'MyDB'.

    Message

    Bypassing recovery for database 'MyDB' because it is marked IN LOAD.

    Several hours had passed and the database had not come online. Note: you can only expand storage online if your data drives are on a SAN infrastructure.

Viewing 6 posts - 1 through 5 (of 5 total)

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