Solved

SQL 2000 Enterprise manager hangs when trying to expand Databases folder tree...

Posted on 2013-01-20
13
947 Views
Last Modified: 2013-02-10
Hi,

Running SQL 2000 SP4 on Windows 2003 box and was upgrading hard drives on array 2 (databases DBF data files) with OS and SQL install on the other array with SQL LDF log files.

I imaged partitions and restored using an imaging utility.

The restore was VERY long but worked.  I had a concern with DRIVE MAPPING as the utility showed my partitions drive letters as DIFFERENT from originals.

I'm aware that if the original drive letters are not the same, that ALL the SQL databases will go into a "SUSPECT" state as they cannot find their MDF or LDF file at specified paths and sometimes DO NOT come back to life.

The plan was to boot into SAFE MODE to make sure SQL services did not run so I could check the drive mappings from the actual OS.

When attempting to boot into safe mode, I missed the F8 boot menu and in a panic I did a hard shut down.

I rebooted, caught the F8 menu, got into safe mode, all the drive letter were correct.  

I booted into normal OS.

I run ENTERPRISE MANAGER, it loads, but when I click on the DATABASES FOLDER, I get an hour glass and it does not expand to show my databases. (all other folders, like DTS, managment, etc all open ok)

It's hung and I close it with TASK MANAGER and get a MMC.exe error in the log.

I tried to access the databases and I CAN access them with applications however they seem a little slow.  I managed open QUERY ANALYZER and checked the status of all the DB's there and they all show as ONLINE.

I tried to switch ODBC on a workstation to a different database, however ODBC hangs at the authentication page.

Does anyone have any idea whats gone wrong here?

Thanks,

MP
0
Comment
Question by:mrpushner
13 Comments
 
LVL 8

Assisted Solution

by:venk_r
venk_r earned 167 total points
ID: 38799544
Did you check the sql error logs? Do you see any erors in that?
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 333 total points
ID: 38799875
where is EM on the same server or remote?
In any case - check on the server event viewer sys\apps logs
additionally to the above posted sql error

check if your sql server service  started - if not start

BTW: did you reboot this box after all ?

And it is possible - you lost your some of user DBs - check for backups

try to open Query analyzer (if sql server service is up) to this sql server  and run sp_helpdb to see what DBs are made it and what do not - >
also it is possible that your DBs are in recovering mode - that take time (if they are big and\or was big transaction during all these operations)

check what you can and post some extra details ...
0
 
LVL 12

Expert Comment

by:NormanMaina
ID: 38800095
What you need to do is install the latest service pack of sql server 2000 - if you already had,just re-install.
The file that needs repair or cannot be found is Sqldmo.dll
0
 

Accepted Solution

by:
mrpushner earned 0 total points
ID: 38801489
Hello,

Wow, thanks for the help you guys, I really appreciate it!

Thinking that a file got corrupt, I did try to re-install SP4, but it hung at the Authentication screen.  I had to kill it.  I did also restart the server a couple times and checked the SQL services which were running.

I did originally checked the SQL logs and must have missed something.  When I went to the App logs, I saw a few RED X's.....

"An error occurred while processing the log for database 'DB2000IH'. "  

Wow, color can really make a difference in whether you notice something or not.

Seems that for this DB, it was trying to recover

Recovery of database 'DB2000IH' (10) is 22% complete (approximately 102 more seconds) (Phase 2 of 3).

But failed with

Error while redoing logged operation in database 'db2000IH'. Error at log record ID (79859:472:8)..

So I went to the LDF file and tried to copy it and got"  Cannot copy file, it is in use" error message.

So I kill the SQL services and I move the LDF for this DB somewhere else.  I restart SQL services, go into ENTERPRISE MANAGER, and WA-LA, I can now expand and see my databases again!!!!!

This DB with no LDF is now in "suspect" mode, so I do a restore and restore from backup I took right before I started all this and all now good, except I aged like 5 years over the ordeal.  (yes, this was a production server)

My question is how in the world can a messed up LDF file WRECK ENTERPRISE MANAGER and prevent me from accessing ALL my other databases?  

That is some SCARY YOU KNOW WHAT.  How can that happen?  Can it happen it ANY version of SQL?

Thanks for all the help, you guys are great.  Sometimes when your are freaked out, simple things like looking at logs to get an idea of what may be causing a problem DON'T present themselves as links or possible solutions.....

I want this to be out here on the web just in case it happens to others....I searched for hours and hours on "SQL Database folder tree will not expand" etc etc etc and found almost nothing related that was a solution.

=)

MP
0
 

Author Comment

by:mrpushner
ID: 38801563
I've requested that this question be closed as follows:

Accepted answer: 0 points for mrpushner's comment #a38801489

for the following reason:

Posts from others gave clues but were not an exact solution but pointed me to the solution.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38801564
you can not just close this after EE posted ideas and pointed you to the source of your problems:
After my post , for example, <<also it is possible that your DBs are in recovering mode - that take time (if they are big and\or was big transaction during all these operations)>

As you said <Seems that for this DB, it was trying to recover

Recovery of database 'DB2000IH' (10) is 22% complete (approximately 102 more seconds) (Phase 2 of 3).
>

---

It means you can not just utilize our time, get help. ideas and just close the Q -
Sorry it is just wrong - if you wish to use our help -- and ask several questions in on - instead of 1 question per request ..
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:mrpushner
ID: 38802388
Hi,  My apologies if somehow some of you took some offense to my "accept as solution".   I'm not out for "points" I'm out to get the solution that worked in my case out here just in case others experience the same thing.

I acknowledged your help that pointed me in the direction of a solution but simply looking at the log did not tell me the exact solution nor the exact reason for the problem.  The database recovery attempts were short, and ended with:

Error while redoing logged operation in database 'db2000IH'. Error at log record ID (79859:472:8)

I'm not aware that its common knowledge that a log error in one database can hang Enterprise Manager to the point you can't access ANY databases from it.  Log errors usually show db's as suspect but don't hang Enterprise Manager.

NOTHING I did (Alter Database commands etc) to this databases worked to take it offline and give me back Enterprise Manager access to my other DB's.  NONE of my user would have been able to switch databases using ODBC either.

I had to remove the LDF file (got me back into EM) and then i restored a backup.  I suppose a rename would have worked as well.  That was the solution to this problem.

Thanks for the help,

MP
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38802430
if you see all posts "Recovery of database" is the key to your problem- it locked EM ..
and the ways to deal with this - or wait, or move , delete - were just extras to your question :

<Does anyone have any idea whats gone wrong here?>

so answer:  "Recovery of database"
0
 

Author Comment

by:mrpushner
ID: 38802442
Hi,

I'm a little confused here, maybe I do not understand EE site politics?

Are you saying you want to possibly delete the question because someone got mad that they did not get credit for a solution?

I don't want any points, credit, or anything from this site.....I want my problem and solution to be posted so that if happens to anybody else they can fix it and not freak out when they can't use Enterprise manager to access and manage their databases.

Moderator, give solution credit to whom ever you choose.

???
0
 

Author Comment

by:mrpushner
ID: 38802482
EugeneZ,

"Recovery of database"  was not the solution to my problem.  It told me nothing about how to fix the problem.  You did not say "move or rename the log file to get back EM".  The database was not still in recovery.  It stopped at 22% and ended with:

Error while redoing logged operation in database 'db2000IH'. Error at log record ID (79859:472:8)

No more logs.

I thanked you all for pointing me in some direction.  I take back my click of accept as solution of my own reply for fear of offending people that try to help me.

The solution to my problem was "move or rename the log file to get back EM".

Moderator, give credit to whom ever you chose.  I think Eugene really wants it.

Thanks,

MP
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 333 total points
ID: 38803180
mrpushner: your Q was : "Does anyone have any idea whats gone wrong here?"
the A: was provided - with solution "wait"
I selected another ways out of this Q scope: if you needed more you must open EE q and not use the same for multiple Q- that you included - and even were provided some additional ideas

and several EE volunteers gave you ideas,. helped to troubleshoot within provided by you info
and I disagree that their(and my) posts did not help you at all.. they show you right direction and actually answer,  gave you keys to resolve your problem believe or not
and even provided you with answers to the extra question that you hosted here as well..
0
 

Author Closing Comment

by:mrpushner
ID: 38872804
These guys here game me some clues, yes, I thanked them for that over and over again.

The solution however is something that I did, namely I shut down SQL services and moved the LDF of a DB that was corrupt which fixed the problem.  No one else suggested that I move or rename any files.

Thanks.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now