Post reply

Database(loading)

  • October 11, 2006 at 3:21 am

    #114729

    Hi, hope someone might be able to help with this sticky one, I have to confess to not being very clued up on the admin side of SQL ....

    I have a database which is stuck at the status of (loading).

    From what I can ascertain it was in the middle of a restore when the restore was cancelled.

    There are various things which have been tried:

    detaching/re-attaching the database, restoring using a different backup, restoring to a new database after deleting the old one, stopping/re-starting the sql server agent, rebooting the server and none have helped.  

    When doing the restore the the progress bar appears although doesn't show anything and then after some time the restore successful message is displayed. The database is then present but without a name and when selecting properties it says it is in the middle of the restore process. After refreshing, the name is displayed but it always goes back to database(loading) status.

    Any suggestions?

    Many thanks

    Dai

  • October 11, 2006 at 9:03 am

    #665248

    Please try

    DBCC DBRECOVER('pubs') -- Replace pubs with database name

     

  • October 12, 2006 at 7:10 am

    #665464

    Hi,

    Appreciate the suggestion however this has not had any effect.

    It seems to be a problem with the restore process throughout the software.

    Back to the drawing board ...

    Thanks

  • October 12, 2006 at 10:36 am

    #665553

    Try:

    sp_resetstatus database_name

    drop database database_name

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • October 13, 2006 at 4:52 am

    #665671

    Thanks for the suggestion but again this has had no effect.

    sp_resetstatus database_name - Gave a message the database did not exist.

    drop database database_name - Then dropped the database as usual so it definitely existed.

    database still stuck at database(loading) and my sanity slowly draining!

  • October 13, 2006 at 10:03 am

    #665781

    Have you run sp_helpdb ?

    The only reason I ask is that your response seems like you may be getting bit by not refreshing the studio ...

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • October 17, 2006 at 2:09 pm

    #666353

    Interesting.  The developers may be using named transactions to allow recovery to that point, similar to a point in time recovery.  The RESTORE will remain in LOADING status if you specify an incorrect named transaction.  These marks are tracked in the msdb which so you should be able to find what marks are available.  To get the database out of loading status, without restoring another transaction log backup run the following

    RESTORE DATABASE DatabaseName WITH RECOVERY

  • October 19, 2006 at 2:09 am

    #666721

    Hi Paul,

    I tried that without success.

    The problem must have been with Enterprise Manager as the following has worked.

    BACKUP DATABASE [dbname] TO  DISK = N'path'
     
    RESTORE FILELISTONLY

       FROM DISK = 'path.bak'

    RESTORE DATABASE dbname

       FROM DISK = 'path.bak'

       WITH MOVE 'dbname_Data' TO 'dbname.mdf',

       MOVE 'dbname_Log' TO 'dbanme.ldf'

    GO

     
    Thanks for everyones replies.

  • October 23, 2006 at 4:44 am

    #667313

    Desmond,

    I have the same problem, but generated by a log-shipping.

    The T-Log files are created and copied correctly but when the Destination Server trys to load it, the database become in "(Loading)" status.

    The sp_helpdb suggested doesn't report it and the database is simply like if it did not exist more...

    I hope that someone has resolved this problem...

    Roberto

  • October 23, 2006 at 12:29 pm

    #667438

    I'm having this problem occasionaly in my log shipping jobs. The problem for me turns out to be corrupted log or full backups. After loading the bad backup the db gets stuck in (loading) and won't allow further logs or full backups to be applied.

    Only way to get it back that I found was to delete the database and restore to a known good backup. Then delete the bad backups before restarting my log shipping jobs.

  • February 13, 2008 at 9:54 am

    #777933

    I realize this thread is way old :ermm:, but for the sake of documentation I came across this same problem. 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'

  • February 14, 2008 at 11:21 am

    #778502

    try to apply logs thru QA, apply one before, proper one and the following tran log. And check for the messages appeared in the results pane. It should give you a pretty good understanding if it is relates to proper tran log or smth else..

  • July 2, 2008 at 1:00 pm

    #837435

    I am having this issue with a database that someone who works with my company decided to restore. I do not know what database backup was used to restore this database.

    I have attempted everything in this thread with the following results (the database name is tempdb2):

    restore database tempdb2 with recovery

    -->File was only partially restored by a database or a file restore. The entire file must be successfully restored before applying the log.

    dbcc dbrecover('tempdb2')

    -->DBCC execution completed. If DBCC printed error messages, contact your system administrator

    ...I saw no errors

    sp_resetstatus tempdb2

    -->Prior to updating sysdatabases entry for database 'tempdb2', mode = 226 and status - 48 (status suspect_bit = 0).

    For row in sysdatabases for database 'tempdb2', the status bit 256 was forced off and mode was forced to 0.

    Warning: you must recover this database prior to access.

    ...tried this over again several times and nothing seemed to work. My tempdb2 database is still (loading). I attempted to drop the database and delete it from enterprise manager, however it throws an error saying that it is being used for replication.

    Anyone else have a clue? I'm stumped 🙁

  • July 3, 2008 at 9:00 am

    #837924

    I had the same problem too. Then I came to know that my backup file was corrupted...So that I restored that database to the second current backup and then I have applied logs manually.

  • December 12, 2009 at 9:05 am

    #1091526

    HELLPP!!!!!!!! I'm having the same problem. Does anyone have a solid answer as to what the problem is.

    I ran "RESTORE DATABASE mydbname WITH RECOVERY" against the database and is reported "The backup was incomplete, you need a complete backup to restore.." or something of that nature.

    I'm presuming it's because the restore did not complete but I'm not sure.

    Thanks ahead,

Viewing 15 posts - 1 through 14 (of 14 total)

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