Required to reboot server to restart database.

[techjohnny]

Our database has always had problems, but simply shutting it down and restarting with the dbman -stop database dbman -start database seemed to clear things up, but now it seems the dbman -stop database is successfully, but will not start again. We get an error message complaining that a process is still in use, which is what I've experienced when the database is still trying to clear out the temporary files.

Unfortunately, the database will only flush these files out after a complete physical reboot of the server.

What type of maintenance run against the database is meant to help clear up this type of problem?

Thanks,

--tj

 

[Rob Fitzpatrick]

Progress version? Platform? Error message text?

Also, I'm not sure which temporary files you're referring to.

 

[TomBascom]

What Rob said.

To which I will add -- the problems that you describe are not in any way normal or expected. They sound more like the application side of things running amok. So you should also tell us what application this is.

 

[techjohnny]

The application is Vantage. We are running an OpenEdge 10.1B hotfix 33 on Windows 2008 Server.

The database is approximately 7.4GB, and no maintenance on the database for at least a year.

--tj

 

[techjohnny]

Our DB is 7GB in size, we have 50 client machine connects, and 2008 x64 running with 8GB RAM.
Here's what our mfgsys.pf start-up looks like:

-Mm 1024 -mmax 65534 -Bt 4096 -s 215 -yy 1970 -stsh 31 -inp 32000 -tok 4000 -TB 31 -TM 32 -D 500 -l 1000 -ttmarshal 5
-T c:\epicor\mfgwrk803
-db c:\epicor\mfgsys803\db\mfgsys
-cpinternal utf-8 -cpstream utf-8 -cprcodein utf-8

 

[Cringer]

Whilst you're at it, can you post the first 70 odd lines of the db log file - the lines that tell you the startup params of the db.

 

[techjohnny]

Log file:
Wed Jun 06 07:43:32 2012
[2012/06/06@07:43:32.276-0700] P-3260 T-3264 I BROKER 0: (333) Multi-user session begin.
[2012/06/06@07:43:33.672-0700] P-3260 T-3264 I BROKER 0: (5326) Begin Physical Redo Phase at 1984 .
[2012/06/06@07:43:42.350-0700] P-3260 T-3264 I BROKER 0: (7161) Physical Redo Phase Completed at blk 2077 off 581 upd 628.
[2012/06/06@07:43:42.353-0700] P-3260 T-3264 I BROKER 0: (13547) At end of Physical redo, transaction table size is 256.
[2012/06/06@07:43:42.355-0700] P-3260 T-3264 I BROKER 0: (7163) Begin Physical Undo 8 transactions at block 2077 offset 617
[2012/06/06@07:43:42.355-0700] P-3260 T-3264 I BROKER 0: (5331) Physical Undo Phase Completed at 2076 .
[2012/06/06@07:43:42.379-0700] P-3260 T-3264 I BROKER 0: (7162) Begin Logical Undo Phase, 7 incomplete transactions are being backed out.
[2012/06/06@07:43:42.380-0700] P-3260 T-3264 I BROKER 0: (11231) Logical Undo Phase begin at Block 2076 Offset 8168.
[2012/06/06@07:43:42.592-0700] P-3260 T-3264 I BROKER 0: (12095) Logical Undo Phase completed at block 2030, offset 3152.
[2012/06/06@07:43:43.149-0700] P-3260 T-3264 I BROKER 0: (5644) Started for 8350 using TCP, pid 3260.
[2012/06/06@07:43:43.209-0700] P-3260 T-3264 I BROKER 0: (8836) Connecting to Admin Server on port 7839.
[2012/06/06@07:43:43.244-0700] P-3260 T-3264 I BROKER 0: (8846) Registered with Admin Server.
[2012/06/06@07:43:43.299-0700] P-3260 T-3264 I BROKER 0: (4234) Progress OpenEdge Release 10.1B build 1372 SP03 TF33 on WINNT .
[2012/06/06@07:43:43.299-0700] P-3260 T-3264 I BROKER 0: (4281) Server started by SYSTEM on batch.
[2012/06/06@07:43:43.300-0700] P-3260 T-3264 I BROKER 0: (6574) Started using pid: 3260.
[2012/06/06@07:43:43.300-0700] P-3260 T-3264 I BROKER 0: (4235) Physical Database Name (-db): c:\epicor\mfgsys803\db\mfgsys.
[2012/06/06@07:43:43.301-0700] P-3260 T-3264 I BROKER 0: (4236) Database Type (-dt): PROGRESS.
[2012/06/06@07:43:43.302-0700] P-3260 T-3264 I BROKER 0: (4237) Force Access (-F): Not Enabled.
[2012/06/06@07:43:43.302-0700] P-3260 T-3264 I BROKER 0: (4238) Direct I/O (-directio): Not Enabled.
[2012/06/06@07:43:43.302-0700] P-3260 T-3264 I BROKER 0: (4239) Number of Database Buffers (-B): 20000.
[2012/06/06@07:43:43.308-0700] P-3260 T-3264 I BROKER 0: (9422) Maximum private buffers per user (-Bpmax): 64.
[2012/06/06@07:43:43.309-0700] P-3260 T-3264 I BROKER 0: (4240) Excess Shared Memory Size (-Mxs): 16424.
[2012/06/06@07:43:43.310-0700] P-3260 T-3264 I BROKER 0: (10014) The shared memory segment is not locked in memory.
[2012/06/06@07:43:43.310-0700] P-3260 T-3264 I BROKER 0: (4241) Current Size of Lock Table (-L): 819200.
[2012/06/06@07:43:43.310-0700] P-3260 T-3264 I BROKER 0: (-----) Maximum Area Number (-maxArea): 32000.
[2012/06/06@07:43:43.311-0700] P-3260 T-3264 I BROKER 0: (4242) Hash Table Entries (-hash): 6661.
[2012/06/06@07:43:43.311-0700] P-3260 T-3264 I BROKER 0: (4243) Current Spin Lock Tries (-spin): 50000.
[2012/06/06@07:43:43.312-0700] P-3260 T-3264 I BROKER 0: (6526) Number of Semaphore Sets (-semsets): 3.
[2012/06/06@07:43:43.313-0700] P-3260 T-3264 I BROKER 0: (13924) Maximum Shared Memory Segment Size (-shmsegsize) 256 Mb.
[2012/06/06@07:43:43.313-0700] P-3260 T-3264 I BROKER 0: (4244) Crash Recovery (-i): Enabled.
[2012/06/06@07:43:43.314-0700] P-3260 T-3264 I BROKER 0: (6573) Database Blocksize (-blocksize): 4096.
[2012/06/06@07:43:43.314-0700] P-3260 T-3264 I BROKER 0: (4245) Delay of Before-Image Flush (-Mf): 3.
[2012/06/06@07:43:43.314-0700] P-3260 T-3264 I BROKER 0: (4247) Before-Image File I/O (-r -R): Reliable.
[2012/06/06@07:43:43.315-0700] P-3260 T-3264 I BROKER 0: (4249) Before-Image Truncate Interval (-G): 0.
[2012/06/06@07:43:43.315-0700] P-3260 T-3264 I BROKER 0: (4250) Before-Image Cluster Size: 524288.
[2012/06/06@07:43:43.315-0700] P-3260 T-3264 I BROKER 0: (4251) Before-Image Block Size: 8192.
[2012/06/06@07:43:43.316-0700] P-3260 T-3264 I BROKER 0: (4252) Number of Before-Image Buffers (-bibufs): 20.
[2012/06/06@07:43:43.316-0700] P-3260 T-3264 I BROKER 0: (-----) Record free chain search depth factor 5 (-recspacesearchdepth)
[2012/06/06@07:43:43.325-0700] P-3260 T-3264 I BROKER 0: (9238) BI File Threshold size (-bithold): 0.0 Bytes.
[2012/06/06@07:43:43.325-0700] P-3260 T-3264 I BROKER 0: (6552) BI File Threshold Stall (-bistall): Disabled.
[2012/06/06@07:43:43.326-0700] P-3260 T-3264 I BROKER 0: (4254) After-Image Stall (-aistall): Not Enabled.
[2012/06/06@07:43:43.326-0700] P-3260 T-3264 I BROKER 0: (4255) After-Image Block Size: 8192.
[2012/06/06@07:43:43.326-0700] P-3260 T-3264 I BROKER 0: (4256) Number of After-Image Buffers (-aibufs): 20.
[2012/06/06@07:43:43.327-0700] P-3260 T-3264 I BROKER 0: (8527) Storage object cache size (-omsize): 1024
[2012/06/06@07:43:43.327-0700] P-3260 T-3264 I BROKER 0: (4257) Maximum Number of Clients Per Server (-Ma): 3.
[2012/06/06@07:43:43.327-0700] P-3260 T-3264 I BROKER 0: (4258) Maximum Number of Servers (-Mn): 44.
[2012/06/06@07:43:43.328-0700] P-3260 T-3264 I BROKER 0: (4259) Minimum Clients Per Server (-Mi): 1.
[2012/06/06@07:43:43.328-0700] P-3260 T-3264 I BROKER 0: (4260) Maximum Number of Users (-n): 137.
[2012/06/06@07:43:43.328-0700] P-3260 T-3264 I BROKER 0: (4261) Host Name (-H): SERVERV.
[2012/06/06@07:43:43.329-0700] P-3260 T-3264 I BROKER 0: (4262) Service Name (-S): 8350.
[2012/06/06@07:43:43.329-0700] P-3260 T-3264 I BROKER 0: (4263) Network Type (-N): TCP.
[2012/06/06@07:43:43.330-0700] P-3260 T-3264 I BROKER 0: (4264) Character Set (-cpinternal): utf-8.
[2012/06/06@07:43:43.330-0700] P-3260 T-3264 I BROKER 0: (4282) Parameter File: Not Enabled.
[2012/06/06@07:43:43.330-0700] P-3260 T-3264 I BROKER 0: (5647) Maximum Servers Per Broker (-Mpb): 32.
[2012/06/06@07:43:43.331-0700] P-3260 T-3264 I BROKER 0: (5648) Minimum Port for Auto Servers (-minport): 3000.
[2012/06/06@07:43:43.331-0700] P-3260 T-3264 I BROKER 0: (5649) Maximum Port for Auto Servers (-maxport): 5000.
[2012/06/06@07:43:43.331-0700] P-3260 T-3264 I BROKER 0: (8864) This broker supports SQL server groups only.
[2012/06/06@07:43:43.332-0700] P-3260 T-3264 I BROKER 0: (9426) Large database file access has been enabled.
[2012/06/06@07:43:43.332-0700] P-3260 T-3264 I BROKER 0: (9336) Created shared memory with segment_id: 42401792
[2012/06/06@07:43:43.333-0700] P-3260 T-3264 I BROKER 0: (12813) Allowed index cursors (-c): 548.
[2012/06/06@07:43:43.334-0700] P-3260 T-3264 I BROKER 0: (12814) Group delay (-groupdelay): 10.
[2012/06/06@07:43:43.334-0700] P-3260 T-3264 I BROKER 0: (12815) Lock table hash table size (-lkhash): 137743
[2012/06/06@07:43:43.335-0700] P-3260 T-3264 I BROKER 0: (12816) Maxport (-maxport): 5000
[2012/06/06@07:43:43.335-0700] P-3260 T-3264 I BROKER 0: (12817) Minport (-minport): 3000
[2012/06/06@07:43:43.336-0700] P-3260 T-3264 I BROKER 0: (12818) Message Buffer Size (-Mm): 1024
[2012/06/06@07:43:43.336-0700] P-3260 T-3264 I BROKER 0: (12820) Maximum Servers per Broker (-Mpb): 32
[2012/06/06@07:43:43.336-0700] P-3260 T-3264 I BROKER 0: (12821) Use muxlatches (-mux): 1
[2012/06/06@07:43:43.337-0700] P-3260 T-3264 I BROKER 0: (12823) Semaphore Sets (-semsets): 3
[2012/06/06@07:43:43.337-0700] P-3260 T-3264 I BROKER 0: (13870) Database Service Manager - IPC Queue Size (-pica) : 64.0 KBytes.
[2012/06/06@07:43:43.338-0700] P-3260 T-3264 I BROKER 0: (13896) TXE Commit lock skip limit (-TXESkipLimit): 10000.
[2012/06/06@07:43:43.340-0700] P-3260 T-3264 I BROKER 0: (10471) Database connections have been enabled.


1) Progress Version: 10.1b hotfix 33
2) Windows 2008 Server SP1
3) What have we tried?
a) Replaced the old OS (2003 SBS Server 4GB Memory)
b) Tried many different parameters
c) dump-n-load several times
4) The most persistent error is "No Servers Available" this has continued from the old server to the current 2008 server. This database was actually migrated from an old Fox Pro database.

--tj

 

[trx]

Try upgrade to 10.1C04. 10.1B on Linux had some user/memory locking related problems - maybe Windows version also has similar problems.
Also, your -B is relatively small comparing to database size and -L is quite big. If you don't use after image then you should implement it ASAP - it is quite easy with aiarchiver in 10.1.

 

[techjohnny]

Thanks "trx" for the response, but unfortunately, our software does not support version 10.1C without a major upgrade.

What would you suggest raising the -B to, and what would you suggest lowering the -L to?

--tj

 

[Cringer]

What major upgrade would that be? Worst case scenario you'll have to recompile the source.

 

[trx]

I am not quite sure whether upgrade 10.1B -> 10.1C requires anything. I am sure that for database itself it is enough to truncate BI in 10.1B and then start database using 10.1C. No need for recompiling. IIRC you don't even have to upgrade clients - at least till you compile code with features available only in 10.1C. And in your logs broker supports only SQL connections - if this is only broker then it should be enough to upgrade database.

With 4k block size and -B = 20k you have around 80 MB allocated for buffer cache. It may be sufficient, but probably -B = 200k and 800MB would be more reasonable - you should observe hit ratio to check whether you should increase it or not (look at Tom's Top Tips at http://dbappraise.com/ppt/ttt.pptx, also it is good idea to check other presentations available on that page). To monitor hit ratio you can use promon or ProTop (http://dbappraise.com/protop.html).
As for -L - high value is not bad per se as it has no negative impact on DB, but high -L suggests there are problems with application itself - probably some really big transactions with too much locking.
-spin may be too high - with modern CPUs it is more reasonable to set it to 10k instead of 50k (old rule of thumb) - see tip #14.
BI cluster size (524288) - seems to be default 512 - you should rather set it to 8192 or 16384 - see tip #13.
Also remember that with enterprise version you can (or rather should) use APW, AIW, BIW.

 

[tamhas]

There is a very large difference between a vendor saying "We don't support anything later than version X of Progress on our version Y" and their saying "anything later than version X Progress won't work with our version Y". They almost never say the second since it is almost assuredly not true. They will say the former because they are too cheap to continue to certify older versions of their software against newer versions of their product, especially since they would prefer you to use the latest version of their product. Unfortunately, that is sometimes an expensive choice if one has made a lot of local modifications. If you have source, it is *highly* likely that you can run on anything up to and including 11.0.

 

[techjohnny]

We cancelled our maintenance last year, and have a copy of 10.1C, but not sure where the control codes are located. Doubt they would resend them if I'm unable to locate.

 

[tamhas]

If it has only been a year, it would not be that expensive to get back on maintenance. Dropping maintenance is a classic penny wise, pound foolish move.