Micros 4.1 SQL DB will not start

[grantsdale]

Hi all,

Having an issue with a customer I recently took over. Its a Micros 3700 (v4.10) setup inside a VM running XP.

They called me a couple hours ago with an issue, saying they couldn't get into the system. I've been working on it, but I can't get Sybase to fire up the SQL DB.

Here are the relevant lines from the log.

Changing [Restaurant] From [OFF] To [DATABASE]
Operational State going from 'OFF' to 'OFF', Target State set to 'DATABASE'
dwWin32ExitCode 0 (0x0) dwServiceSpecificExitCode 0 (0x0)
***ERROR*** Failed to start SQL Engine as a service.
***ERROR*** Will rest a bit and try to start it again.
dwWin32ExitCode 0 (0x0) dwServiceSpecificExitCode 0 (0x0)
***ERROR*** Failed to start SQL Engine as a service.
***ERROR*** Will rest a bit and try to start it again.
Op step changed from [0] to [1] after [runDBMS] [FAULT]
dwWin32ExitCode 0 (0x0) dwServiceSpecificExitCode 0 (0x0)
***ERROR*** Failed to start SQL Engine as a service.
Failed to start SQL Server


I've tried manually starting the service as well, no luck.
I don't know what to even try.

Thanks for any help.

 

[grantsdale]

I should mention that this started after maint/EOD last night.

 

[Mauriciose]

Hi grantsdale, just to take a context into... did you try to make login in Sybase Central?. In Sybase Central you can to startup the service to the App.

Good Luck

Mauro

 

[grantsdale]

Yes, I have. Unfortunately - unless I'm doing something wrong - it wants the encryption key to start it from there, which I do not have.

 

[DJensen999]

First, check the "path to executable" part of the sql(ServerName) definition? On XP, it should look like:

C:\Program Files\SQL Anywhere 11\Bin32\dbsrv11.exe -hvSQLANYs_sqlSERVERNAME

Second thing to check would be the ODBC Datasource definition called "Micros." Verify on the login tab that Database file points to the current Micros.db location, the database name is "micros", the server name is the name of the Sybase SQL Windows service above, and the start line is C:\MICROS\Common\Bin\SrvcStartDB.exe.

If the encryption key is missing, you may need to restore from the latest backup.

 

[grantsdale]

First part looks good.

Second part - the login tab where? I can't restore from backup because I can't get the DB service running. Otherwise I would have tried that already.

Thanks.

 

[DJensen999]

ODBC datasource information is in Control Panel under ODBC - if it's missing, try "odbccp32.cpl" or "odbcad32.cpl" in Start-Run.

 

[grantsdale]

Can't find it there but I can get to it from Sybase Central. Everything looks as it should, but when I try to do 'test connection' from there I get 'database server not found'.

If I try to start the sql service manually from services, it tells me it starts and then stops.

 

[Mauriciose]

Well... can sound like too basic, but...could you try these?

1) Change your user login to Windows sesion. Try with other user with Admin privileges. If you don't have it, create new one.
I think that your user can't have the privilege to start up sybase services.

2) Verify that your user can run bat jobs in the Local Security Settings/User Rights Assigments\Log on as a batch job.

Finally, you say that Micros is run on VM with WinXP... did you change the IP address or the server name?. If you did this, then you must to change to original name or IP... just ideas to help you.

Good Luck!

Mauro

 

[DJensen999]

Taking a wild guess here - what user is the Sybase SQL service set to run under?

 

[grantsdale]

@Mauriciose
1) Tried it (and just again after I read your suggestion.) No change. Have tried a couple of users, including newly created ones.
2) I can.

Did not change PC name or IP. Still the same.

@DJensen999: Right now I just created a new user for it of 'test'

 

[DJensen999]

I haven't touched the XP versions of Micros in awhile, but can you try running the Sybase SQL service as "Local System"? That's the out-of-the-box default, that I'm aware of.

 

[grantsdale]

Yup, I've tried it - doesn't change anything. Still starts and stops.

 

[DJensen999]

Without putting my hands on it, I'm about out of suggestions. Perhaps moregelen or pmegan may have some additional ideas...?

 

[pmegan]

Gotta love those helpful error messages. Have you looked in the windows event viewer to see if there's anything more helpful? Sometimes, but not too often, you'll get a pleasant surprise there.

Does your VM automatically generate snapshots? If it does, and you have a recent one, rolling back would probably be the fastest and cleanest way to get backup and going.

If you don't have a snapshot, make one so you have a fallback in case things get worse.

It sounds like either the database, log, or both may be messed up. I'd try to 1) restore a backup, and 2) force the db up and create a new log.

You can restore a backup through a command prompt, using the -U upgrade switch. This usually restores a backup and upgrades it to the version loaded on the server.
[ol 1]
[li]Create a new folder and move micros.db and micros.log into it.[/li]
[li]Get the full path and filename for the .mbz backup you want to restore, (called <backuppath> in the next step)[/li]
[li]Open a command prompt and type in DM -U "<backuppath>" (the double quotes aren't strictly needed, but I use them)[/li]
[li]At this point Database Manager should open up, restore and maybe rebuild your DB. Since it's already at the current version, the upgrade part won't run and you should be able to reboot and start everything in the control panel.[/li]
[/ol]

If that doesn't work, or if you don't have a backup, try force generating a new log.
[ol 1]
[li]If you haven't yet, create a new folder and move micros.db and micros.log into it.[/li]
[li]Copy just the micros.db file back into \micros\database\data.[/li]
[li]Open a command prompt and run this: DM -F %RESDBDIR%\micros.db[/li]
[li]You'll probably see that little SQL lightening bolt opening and closing in the task bar. I haven't done this in a while, so don't remember if you'll get an alert telling you that it's done, or if the SQL tasks just end and you hope it worked, but when it's done open the control panel and try to bring the system to Back Of House.[/li]
[/ol]

They're both ugly things to do when the database is faulting, that's why I'm suggesting the snapshot.

Good luck

 

[grantsdale]

pmegan - Thank you!!!

Huge progress, although not there yet.

I used the second method (the first didn't work, unfortunately.)

After that, I am able to run all the programs on the server, get the SQL up and running, and access the db.

However, when a terminal comes online, it asks if it wants to be placed into standalone mode, and when you choose 'no', the SQL server crashes.

Log has the following:

SQLConnect: (08001)[Sybase][ODBC Driver][Adaptive Server Anywhere]Database server not found
SQLConnect: (08001)[Sybase][ODBC Driver][Adaptive Server Anywhere]Database server not found
SQLConnect: (08001)[Sybase][ODBC Driver][Adaptive Server Anywhere]Database server not found
Error getting update list

I believe this is the corresponding error from Event Viewer:

Event Type: Error
Event Source: ASA 9.0
Event Category: None
Event ID: 1
Date: 10/20/2014
Time: 3:32:46 PM
User: N/A
Computer: MICROSPOS
Description:
The description for Event ID ( 1 ) in Source ( ASA 9.0 ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: SQLANYs_sqlMICROSPOS, *** ERROR *** Assertion failed: 201501 (9.0.2.3586)
Page for requested record not a table page or record not present on page.

At least I'm going in a direction now.

 

[grantsdale]

So it seems that the workstation terminals CAN interact with the database, but the DB crashes out at some point.

Autosequence 10202 step 1 of type S has begun.
Post Time Period Sales
{CALL MICROS.sp_R_time_period()}
CSeatingSectionLayout::UpdateLastActiveLayout FAILED - TMSProxyInstance.GetRvcStatus - rc[3568592]
SQLExecDirect: (S1000)[Sybase][ODBC Driver][Adaptive Server Anywhere]Internal database error *** ERROR *** Assertion failed: 201501 (9.0.2.3586)
Page for requested record not a table page or record not present on page -- transaction rolled back
SQLTransact: (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Connection was terminated
Error in call to DbNextOpenCheckSummary statement
ClientState COMMIT failed. Error:
SQLExecDirect: (S1000)[Sybase][ODBC Driver][Adaptive Server Anywhere]Internal database error *** ERROR *** Assertion failed: 201501 (9.0.2.3586)
Page for requested record not a table page or record not present on page -- transaction rolled back
SQLExecDirect: (S1000)[Sybase][ODBC Driver][Adaptive Server Anywhere]Internal database error *** ERROR *** Assertion failed: 201501 (9.0.2.3586)
Page for requested record not a table page or record not present on page -- transaction rolled back
SQLTransact: (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Connection was terminated
SQLFreeConnect: (S1010)[Microsoft][ODBC Driver Manager] Function sequence error
Autosequence 10202 step 1 of type S has encountered a stored procedure error...Error executing stored procedure.
CDbPool : Flushing Connection Pool
Step completed.
Autosequence 10202 step 1 of type S has completed in 1 seconds.
Autosequence 10202 step 2 of type S has begun.
SQLExecute: (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Connection was terminated
Error In QueryEngine::Execute_spSQLExecute: (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Connection was terminated
Autosequence 10202 step 2 of type S has encountered a stored procedure error...Error In QueryEngine::Execute_spSQLExecute: (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Connection was terminated
SQLTransact(ROLLBACK): (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Communication error
SQLTransact(ROLLBACK): (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Communication error
CDbPool : Flushed 2 Connections from Connection Pool
SQLConnect: (08001)[Sybase][ODBC Driver][Adaptive Server Anywhere]Database server not found

I don't know if I need to clear logs or the db or something. I have turned off autosequence for now but I don't know if thats doing more harm than good.

 

[pmegan]

Yeah, that's not going to help for very long. You're tanking when time period totals are posting. That happens automatically when a time period report is run, so there's a good chance the db will shut down the next time that happens.

Since there's a snapshot, I'd try rebuilding the db through Database Manager. That error sounds like it could be kind of ugly; maybe a broken page or some other db corruption. If that doesn't work you may have to reload 4.1

 

[grantsdale]

Three problems

1 - No snapshot. The idiot that did this setup installed VMWare Player instead of Workstation, so I'm limited on what I can do. I'll fix it eventually, but can't really worry about that at this point. I did a manual backup of the VM, which will have to do for fallback purposes.

2 - When I load DM, I get nothing. I have the buttons on the right, but everything's grayed out. I think the user (the only admin they know of, the actual manager who used to do this is long gone) doesn't have access.

3 - Doesn't look like they have the installer for this build. I found all kinds of older stuff (and I have some newer from other clients) but I don't see this build on the machine. I might have it, but I'm still looking.

 

[grantsdale]

Ok, I did find the RES Update for 4.1. I tried just running that again, didn't change anything.