IN LOAD state for a backup of a database

Question

Post reply

IN LOAD state for a backup of a database

adamcrv

Hall of Fame

Points: 3056
More actions
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

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

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

mccork SSC-Insane Points: 22288 More actions · Answer 1

Try:

restore database YourDBName with recovery

Cheers,

- Mark

Cheers,
- Mark

adamcrv Hall of Fame Points: 3056 More actions · Answer 2

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

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]

adamcrv Hall of Fame Points: 3056 More actions · Answer 4

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

ckempste SSCoach Points: 17983 More actions · Answer 5

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"

adamcrv Hall of Fame Points: 3056 More actions · Answer 6

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

adamcrv Hall of Fame Points: 3056 More actions · Answer 7

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

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]

adamcrv Hall of Fame Points: 3056 More actions · Answer 9

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

mccork SSC-Insane Points: 22288 More actions · Answer 10

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

Cheers,

- Mark

Cheers,
- Mark

adamcrv Hall of Fame Points: 3056 More actions · Answer 11

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