b1 file suddenly grows to 10x, 20x, 30x database size

[Devinport]

I've been scratching my head and trying to read whatever I can find... I think there's a rogue transaction at the root, There is only Epicor code updating the database, all odbc clients are in read uncommitted, so there's no transactions from users.. I do not believe it to be a database tuning prob...If anyone has ideas, I'd appreciate it. (There's a very high bridge nearby...but I'd probably just end up breaking my neck ;-)

I have a 10.1B SP3 HF33 database F:\mfgsys, on a raid 0 pair of velociraptor drives, completely isolated... running windows 2008 R2, hex core, 8G box. It's generally very snappy and performance is great, any normal query executes in < 0.2 sec...and when all is well my b1 file is about 20-25% of my d1,(I've been experimenting with bi cluster and block sizes with no improvement)...about every 3-5 days, a _proapsv.exe begins to run continously and the b1 file will grow indefinitely until I kill the offending _proapsv.exe process... shutdown... truncate, which can take an hour if I don't get to quickly and the b1 file continues to grow to about 150% of its size when I shut down, truncating a 50G b1 is not a pleasant on a production system. I got so desperate I wrote a small script to monitor the b1 file and email me if it grows even 1 byte).

Here's some details...
[TABLE="width: 100%"]
[TR]
[TD]Directory of F:\


04/20/2012 09:58 AM <DIR> archive
04/13/2012 07:52 AM 53 backup.bat
05/01/2012 01:10 PM 268,697,600 mfgsys.b1
05/01/2012 01:10 PM 1,222,770,688 mfgsys.d1
05/01/2012 01:06 PM 32,768 mfgsys.db
05/01/2012 01:46 PM 49,832 mfgsys.lg
05/02/2012 08:00 AM 182,858 mfgsys.lic
05/01/2012 01:10 PM 38 mfgsys.lk
10/02/2011 10:32 PM 81 mfgsys.st
03/27/2012 08:15 AM 1,050 startsvrs803.cmd
04/17/2012 10:53 AM 39 stats.bat
03/19/2012 12:23 PM 1,066 stopsvrs803.bat
11 File(s) 1,491,736,073 bytes
1 Dir(s) 1,198,398,373,888 bytes free
[/TD]
[TD]
[TABLE="width: 500"]
[TR]
[TD]Database Status:
Database version number: 4246
Database state: Open (1)
Database damaged flags: None (0)
Integrity flags: None (1536)
Database block size (bytes): 4096
Total number of database blocks: 298534
Database blocks high water mark: 298489
Free blocks below highwater mark: 38
Record blocks with free space: 1214
Before image block size (bytes): 16384
Before image cluster size (kb): 65536
After image block size (bytes): 8192
Last transaction number: 80420116
Highest file number defined: 0
Database created (multi-volume): 10/27/11 11:28
Most recent database open: 05/01/12 13:10
Previous database open: 05/01/12 13:10
Local schema cache time stamp: 10/29/11 13:22
Most recent .bi file open: 05/01/12 13:10
[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[/TABLE]

 

[jdpjamesp]

Using Progress Explorer you can get all the different AppServer details up. Can you narrow down which one it is using the PID of the process? That might give you more of a hint as to what is misbehaving.

 

[Devinport]

Sounds like an excellent idea... I'm fairly certain it is a client appserver, it seems to coincide when operators are having difficulty ending an activity. I'll capture the next incident in greater detail. Thanks!

 

[cj_brandt]

If you have the ability to upgrade to 10.1C, you can see the code the app server is executing. I think that would really help you in this case.
If you have AI enabled, you can go back through the AI files using scan verbose and try to get an idea what is happening.

I would look at active transactions - either through VST or promon R&D -> 1 -> 4 -> 3. Just make sure your problem isn't due to active transaction.

The old parameters bistall and bithreshold might be helpful for you in this case as well.

 

[Rob Fitzpatrick]

I agree on -bistall and -bithold, it will prevent your BI growing forever and filling the disk. You need to determine why your BI file is growing, and also keep it from reaching an unmanageable size.

The BI file is essentially a ring buffer. It is a doubly-linked list of BI clusters. Initially it is four clusters in size, and may grow over time based on the demands of the application(s). The blocks in those clusters will be reused over time, if possible. "If possible" means that the next available cluster in the ring, if it already contains BI notes, is not in use by any active transactions, and any data blocks that were modified when the notes were written have been flushed to disk. This means that if you have even one client with one active transaction for which BI notes have been written, then the BI cluster in which those notes have been written can never be reused (until the conditions preventing re-use no longer exist). That in turn means that when the existing BI clusters are full, a new cluster will be added to the ring; the BI file is extended. As new transaction notes for other clients are written to the BI it will keep extending indefinitely, until you hit a boundary condition like DB shutdown, exhaustion of BI partition disk space, hitting specified BI threshold, or the client in question commits or rolls back the problem transaction.

As CJ said, look for your oldest transaction(s) and determine which client(s) they belong to. You will then have some application troubleshooting to do to determine why you have this issue, e.g. starting a transaction and stalling, or going into an infinite loop, or blocking on I/O, etc.

On a side note: a database on a RAID 0 array? Performance may be good, but in my book that's living dangerously. You're twice as likely to suffer media failure, and therefore downtime and data loss. I hope you have at least put your backups and filled AI extents on a different disk in the short term, and on a different box soon after.