msdb (Loading) problem 2

[alan12345]

I have a test SQL Database. No critical data in it.

I was restoring msdb the other day and cancelled it in the middle of a restore.

Ever since, I see the msdb databas greyed out and msdb (Loading) next to it. I get messages saying that msdb is in the middle of a restore.

What can I do? I don't mind losing my databases on my local machine and starting over.

Thanks for your help!

Alan

 

[SQLBill]

Try running this command first:

RESTORE DATABASE MSDB WITH RECOVERY

That should take it out of the Loading state. Then detach it, rename the .mdf and .ldf files (I usually change the names like this mydb.mdf.old and mydblog.ldf.old). Then do the restore again, but this time don't stop it. Once it restores the MSDB, you can then delete the .old (or whatever you named them) files.

-SQLBill

Posting advice: FAQ481-4875

 

[JohnBates]

alan12345,

If it's SS 2000, these steps will reset the Loading status:
You may have to be logged in as sa or at least have SysAdmin permissions.

Step1.
The trick is to change the system table, but to do that, you have to first
Change a SQL Server setting to allow system tables to be modified…

In Enterprise Manager, right-click Server name à Properties


Click ‘Server Settings’…..
Under ‘Server Behaviour’, check-off the ‘Allow modifications to be made to system catalogs’ box

2. Next, open the sysdatabases in the master database.

Find the row containing the database you restored to.

If the Category column contains ‘1’, change it to ‘0’


3. Refresh the list of Databases in Enterprise Manager.
If may still say ‘(Loading)’ but usually you will be able to delete it now.

HTH, John

 

[alan12345]

John:

I tried this. the Category was already set to 0. Msdb is still greyed and locked.

Can I disconnect and delete my mdfs?

BTW I am using MSDE Desktop engine. Would this make a difference?

Thanks,
Alan

 

[Catadmin]

Have you tried SQLBill's suggestion yet?

The other suggestion I have is to install a new instance and then remove the old instance (if you really don't mind losing everything).

Catadmin


Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[alan12345]

Yesterday, I uninstalled MSDE and reinstalled it.

That fixed everything.

thanks for your help!

Alan