Alter Database Not Responding

bstapylton

Hi guys,


By all likelihood by the time I get a reply about this - I will have either killed the process or it might have magically completed.


Long story short: I am trying to get Web Services listening when I got this error message from http://[computer]:7047/DynamicsNAV/WS/Services:


"The [Database Name] database on the [Server Name] server is not enabled for Microsoft Dynamics NAV Classic. You must enable Microsoft Dynamics NAV Server with Microsoft Dynamics NAV Classic. On the File menu, click Database, and then click Alter. Choose the Options tab and select Microsoft Dynamics NAV Server.


So I did what it asked, altered my database in the classic client, ticked "Single User" and "Enable for Microsoft Dynamics NAV Server" and hit OK.


That was about 2 hours ago. The client is "Not Responding" according to Task Manager, and there's really nothing going on with the process as far as the CPU/Memory is concerned.


I really don't like the idea of closing the process when it may just be doing some extremely long processing.


What is the ETA on altering the DB for NAV Server usually? Is there a reason it would take more than two hours?


If I go to the WSDL page now I get this error message instead (while the process is still running but Not Responding):

"The following ODBC error occurred: Error: [Microsoft][ODBC SQL Server Driver]Database is invalid or cannot be accessed State ID: HY024"



That just tells me that the "Single User" is still in play to protect the database.


Any help?
Thanks

bstapylton

I should add:


I decided to perform the same task on a backup of the same database on another server.

This time the alter database has started to compile objects (almost immediately after clicking OK I might add) which my current version didn't do or prompt me about.


So it's now my belief that the process is just locked up somehow.


If this process works on the other server I may just do a backup/restore onto this server and cross my fingers.


Things never seem to just work first go, do they?

bstapylton

With a Keep It Simple, Stupid (KISS) approach I decided to break it down.

NAV is performing two tasks here. First, it is setting the database to Single User mode - which is actually where it was halting... waiting for the database to be clear.


I had to stop both Microsoft Dynamics NAV services on the server to get the Single User mode to work. Now my database is finally performing this object compile job


Thanks to Mibuso and viewtopic.php?f=23&t=25799 for this breakthrough hehe.



I hope this thread serves others in the future. In the future I'll stop thinking outside the box about what might be wrong, and look directly at what might be happening behind-the-scenes.

omard

Cheers! Saved me a headache!! \:D/

DenSter

I'd suggest using SSMS to set the database to single user mode. For some reason, the NAV client can get blocked during this task.

grapp0

Nice ! This was very helpul ! :thumbsup:

bbrown

DenSter wrote:

I'd suggest using SSMS to set the database to single user mode. For some reason, the NAV client can get blocked during this task.

I agree. The database cannot switch to "single user" if other users are connected. The NAV client will just sit there "not responding" until the other users are logged out. SSMS will quickly give you a useful message that there are others in the database. Much more user friendly.

matttrax

If your NAV client just sits there you can also run a query of "sp_who" to see everyone in the database. You can then see what session is blocking you and run "kill session_number" where session_number is the id of the session you are being blocked by. This is all done inside SQL Management Studio.

bbrown

Actually if you do it from SSMS, it force closes all the other connections.

DenSter

And all of that in a matter of a split second SSMS is the way to go for setting the database to single user.

dianagele

Is the server 64 bit or 32 bit?: xp_ndo.dll or the xp_ndo_x64.dll?
look at the main server:
system databases --> Programmability --> Extended Stored Procedures. what does sp_ndo_enumusergrops?