"Bergquist, Brett" <bbergqu...@canoga.com> writes: > I have a database in production that has been running fine for a few > years. It started out having about 100K inserts per day into it and > now is up to about 4.6M inserts per day and this has been working > fine. > > Tonight the customer called because the system was chewing up disk > space. I had the customer restart the database engine and it is taking > a long time to boot the database. I had the customer check the "log" > directory in the database and there were 62K ".dat" files present. > > So I am assuming that these are for transactions that have not > committed, correct?
Yes, but they are not cleaned up until a checkpoint has run (by default that happens when you have 10MB of transaction log), so they may contain committed transactions too. And the checkpoint will only delete log files older than the oldest transaction that's still alive. > But for the life of me, I cannot figure out what > transaction could have been in progress and not committed since July > 12'th. It seems to me this would have exhausted memory or some other > resource by now. > > One other point, an online database backup is done each night by the > customer. Could this trigger anything like this? Yes, an ongoing online backup will prevent deletion of log files, since it needs them to track modifications that happen while it copies the database. It could also happen if log archiving has been enabled (using the SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE procedure). You can tell whether log archiving is enabled by looking for a line that says derby.storage.logArchiveMode=true in the service.properties file in the database directory. > Tonight when running > a utility against the database, the utility failed to acquire locks, > but there should have been nothing else running but this utility and > it is single threaded, so there should have been no lock contention. > It also acts like there is a database backup that is still on going... I don't think an online backup needs many locks. If you connect to the database using ij and execute SELECT * FROM SYSCS_DIAG.LOCK_TABLE you should see which locks are held, which might give some clues. > Right now, I am just waiting for the database to cleanup and boot so > that I can get in and examine it. Is there any shortcut or express way > to to boot the database? Is there any way to monitor the progress of > this boot cleanup? I don't know of a way to speed it up. There is a flag that makes debug builds print more info to derby.log during the recovery phase (-Dderby.debug.true=LogTrace, I think), but it may be too low-level to get much useful info in this case. > Any thoughts or pointers in trying to figure out what is going on will > be greatly appreciated. > > The database in question is Derby 10.5.1 > > Brett -- Knut Anders
