Post reply

IN LOAD state for a backup of a database

  • September 15, 2003 at 12:02 am

    #59012

    Hi guys !

    Occassionaly we receive backups of dbs from our clients to debug specific problems they are having with the system. One of them came through today that is "stuck" in the "IN LOAD" state. How the heck do you take it out of this state, or is it even possible too.. This doesnt sound like a good state to be in for a db when doing a backup.. Any ideas ? Done a search over most of the net (google) and have come up with not much !.. thanks for any help you can provide !


    ------------------------------
    Life is far too important to be taken seriously

  • September 15, 2003 at 12:40 am

    #474114

    Try:

    restore database YourDBName with recovery

    Cheers,

    - Mark


    Cheers,
    - Mark

  • September 15, 2003 at 1:12 am

    #474119

    Thanks Mark but no joy unfortunately. the error i get is as follows when running that from qa.

    "Could not find database ID 34. Database may not be activated yet or may be in transition."


    ------------------------------
    Life is far too important to be taken seriously

  • September 15, 2003 at 1:15 am

    #474120

    quote:

    Thanks Mark but no joy unfortunately. the error i get is as follows when running that from qa.

    "Could not find database ID 34. Database may not be activated yet or may be in transition."

    have you tried something like

    select dbid, name from master..sysdatabases order by dbid

    to check if there is an entry for this db?

    Frank

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • September 15, 2003 at 1:22 am

    #474121

    Hi Frank,

    yes there is an entry in the sysdatabases. Thats why i thought it was weird it would give an error like this. This isn't an urgent issue. but just annoying is all at the moment...


    ------------------------------
    Life is far too important to be taken seriously

  • September 15, 2003 at 1:23 am

    #474122

    Hi there

    This is a pain in your you know what message.

    Try this: http://support.microsoft.com/default.aspx?scid=kb;en-us;819264

    the restore post re NORECOVERY is a fair comment as well, a pitty it didnt work for you though as it would have been the easiest 🙂

    Anything in your sqlserver logs btw?

    The DB may be locked for some internal reason related to the restore (obvious enough perhaps), so why not re-start it?, did you shutdown and re-start the instance - if so, did the db change its "mode" ?

    Cheers

    Ck

    Chris Kempster

    http://www.chriskempster.com

    Author of "SQL Server 2k for the Oracle DBA"


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • September 15, 2003 at 1:27 am

    #474124

    Also

    To shed more light on the subject..

    There is a null version number in sysdatabases all the others are 539

    and its status is 1077936145

    Whereas the others are typically 4194304


    ------------------------------
    Life is far too important to be taken seriously

  • September 15, 2003 at 1:31 am

    #474126

    ckempste, Will have to wait until tommorrow. HAve people working on other projects related to the SQL server. They all get a little twitchy if i just switch it off and restart it on them I will look into that kb and more stuff though tommorrow.. I will report back to you guys when i get to the bottom of this particular mystery...


    ------------------------------
    Life is far too important to be taken seriously

  • September 15, 2003 at 1:33 am

    #474128

    quote:

    Also

    To shed more light on the subject..

    There is a null version number in sysdatabases all the others are 539

    and its status is 1077936145

    Whereas the others are typically 4194304

    4194304 is for 'autoshrink' whereas 1077936145 seems to be a combination of (1073741824 'cleanly shutdown + 4194304 'autoshrink'+ 16 'torn page detection + 1 'autoclose').

    515 version is for SQL7 and 539 must be SQL2k. NULL shouldn't really be there

    Frank

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • September 15, 2003 at 5:33 pm

    #474271

    Hi people !,

    With a new day comes new successes.

    I have been able to successfully open the db by shutting down the sql server and starting it up again. Crazy but true 🙂

    Thanks to everyone who helped here !

    I sorta think that there should have been a way to do this without restarting the server. But im glad that it has been fixed 🙂

    Thanks again !


    ------------------------------
    Life is far too important to be taken seriously

  • September 15, 2003 at 8:21 pm

    #474290

    Just out of interest... any relavent errorlog messages generated for that database when you started sql?

    Cheers,

    - Mark


    Cheers,
    - Mark

  • September 15, 2003 at 8:59 pm

    #474295

    The only error i got in the log was before I restarted sql and that was while i was trying to access the database

    "Bypassing recovery for DBNAME because it is marked "IN LOAD" "

    That is in the error log the only other error i got was when I tried to access the database which is when i got the "Could not find database ID 34. Database may not be activated yet or may be in transition." in a dialog box. As you can see not exactly the most helpful..!

    Hopefully this will help someone else if they get a similar issue in the future

    Oh btw i was running SP3 for SQL...

    Thanks once again all..


    ------------------------------
    Life is far too important to be taken seriously

Viewing 12 posts - 1 through 11 (of 11 total)

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