Rebuilding Micros Database 1

[Omidaze]

I am the property expert for a Micros 3700 system at at restuarant. From time to time the systems gets slow, and when its time to print server reports it takes up to 10 minutes rather that the usual 10 seconds.

I need to know if rebuilding the database will help to make the system run more efficent. Also is it safe to run a datbase rebuild without losing any data? what does a databse rebuild actually do?

Thanks much

 

[pmegan]

I'd take a look at the incremental posting autosequence before rebuilding. If the reports slow down for a while and then fix themselves, your problem is probably under-scheduled posting.

What version of 3700 do you have?

The rebuild does the following:
renames the database/log to microsold.db/microsold.log
dumps the non-null data to text files
scripts the entire database structure
runs the script to create a fresh, blank database
loads the table from the text files


You can try rebuilding the database, especially if your system has ever had purging problems. When the system can't purge totals it keeps grabbing more disk space to store new totals. Once the problem is fixed and it purges again, the disk space is not returned to the O/S, so you have a big bloated database full of null lines.

Make a backup right before you start and give yourself plenty of time. A medium sized 3700 db, 200M or so, can take a couple of hours.

Hope that helps,
Pat

 

[Omidaze]

Thanks for your reply Pat.

What is the recommended time for incremental posting autosequence?

 

[pmegan]

The timing depends a bit on the system you have. The older version weren't always as effecient as they could be so posting could sometimes take a little while. The idea is to get the incremental posting to run pretty often without overlapping. If you don't have an incremental posting procedure running, set ou up to go off every 2 hours or so. Take a look at the 3700d log every day to get an idea of how long the posting is taking. After a few busy days adjust it to an increment that's 1.5 to 2 times as long as it takes to run during busy periods. Watch the logs for a few days and see how it goes.

One thing I forgot to mention earlier - Micros added some posting procedures a few versions back for time period labor totals and a few others that were called from the report templates. They're in the 130/140 number range when setting up autosequence steps. Take a look at any Crystal Report templates you're using and make sure the embedded posting procedures are part of your incremental posting.

Pat

 

[Omidaze]

Hello Pat

Thanks again for your replies. So this database has not been rebuilt since the system was installed over 3 years ago. do you recommend a database rebuild? have you known of any complications with the rebuild. I just dont want to lose any of the data. I know that I need to back up the database just in case but I want to prevent any problems.

thanks

 

[pmegan]

As long as you have a backup there's no harm in rebuilding it. I've been working with the 3700 database for 9 years and can only think of a few times when the rebuild completely tanked and those were due to physical corruption on the hard drive. At that point you just drop in the backup and you're back in business. It's a good idea to run scandisk /r and reload the 3700 application and service packs if that happens though.

 

[Omidaze]

Hello, Pat.

So I did the rebuild. There were some errors however I continued the rebuild when it was done I started the system and everything seems to to be running ok. I did a database validation and it came up with 6 errors:

1. Run time SQL error -- Foreign key CFK7DEV_DEF has missing index entries.

2. Run time SQL error -- Foreign key CFK4EMP_DEF has missing index entries

3. Run time SQL error -- Foreign key CFK1INTERFACE_DEF has missing index entries

4. Run time SQL error -- Foreign key CFK99TAX_RATE_DEF has missing index entries

5. Run time SQL error -- Foreign key CFK3TIME_CARD_DTL has missing index entries

6. Run time SQL error -- Foreign key CFK1UWS_PERIPHERAL_DEF has missing index entries

Can you explain what these errors are related to?
Also how can you tell the maximum amount of terminals a 3700 res 3.0 foundation level 2 system can have?.

Thanks

 

[Omidaze]

Hi, Again

Have you done a 3.0 to 4.0 upgrade before. If so what what is the procedure?

 

[pmegan]

Hi Omidaze,

Sorry it took so long to get back to you. I've been rolling out upgrades so haven't been able to get on the forums for a while.

Primary keys are used as a unique identifier for database records. Foreign keys are used to ensure data integrity between tables. For example, menu items are held in micros.mi_def and the prices are held in micros.mi_price_def. You can't have a price without a menu item, so there's a cascading foreign key set on micros.mi_price_def that links the mi_seq column to the mi_seq column in the micros.mi_def table. This ensures that there can't be a record in the price table with a mi_seq value that does not exist in the menu item table.
In this case micros.mi_def is the parent table and micros.mi_price_def is the child.
Cascading means that when a record is deleted in the parent table, all records in the child table linked to that parent item will also be deleted.
If the FK isn't cascading, you'll get an error when you try to delete a parent record that has children. This is how most of the relationships in micros are set up.
Sometimes, through db corruption or careless dba queries, a parent will get deleted while there are still child records. These children are now orphans, with a link to a parent record that doesn't exist. It sounds like this is what you have going on.

The foreign keys are all named for the table they restrict. So CFK7DEV_DEF is a foreign key on micros.dev_def. You can see exactly what it is by logging into Sybase Central, selecting the table you want and going to the Foreign Key tab. The FK description will tell you what table it refers to and what columns are used in that link. For example:

CFK7DEV_DEF links the lan_node_seq and com_port_seq columns in the dev_def table to the matching columns in the com_port_def table. The query below will give you the records in the device table that are linked to com ports that don't exist. The rebuild should have omitted these records, but if you run it on the pre-rebuild db you should get results.

select * 
from 
  micros.dev_def d left outer join
  micros.com_port_def c on
    d.lan_node_seq = c.lan_node_seq and
    d.com_port_seq = c.com_port_seq
where
  c.com_port_seq is null

I'd take a look at each foreign key, see what it links together and make sure everything looks ok in the configurator for that data. For the above example, take a look at the Com Ports in the Network Node setup. You may have one that's missing. Also check out the Devices and User Workstation|Peripheral setups. They're probably all stemming from that missing com port record.

 

[pmegan]

Upgrading from 3.0 to 4.0 is a bit of a pain. You have to upgrade to 3.2 first and then to 4.0. There's also a problem converting totals up, so you're much better off clearing totals before converting.

After the conversion is done you'll have to update some employee privileges and external program command lines. These are all included in the Res 4 documentation. I've also found that most of my reports with embedded posting procedures aren't actually calling those postings before running, so I've had to add steps to a lot of autosequences. It's definately an adventure.

Pat