Service Manager SQL Transaction log grows too large

After a week or 2, the SQL transaction log grows to 25GB or more (same size as the database)
We have identified that this is because SQL is using Full Recovery mode so that in the event of a databse failure, we can recover the database and lose less than 1 hour of input.
We have scheduled (hourly) SQL jobs to backup the transaction log.
Normally this job would truncate the SQL transaction log, but although the jobs run without error, the log file is NOT truncating as it should.
We have identified that this is because there are a number of "implicit transactions" open, and it is clear that Service Manager is responsible for these transactions
Specifically these are:-
 SET FMTONLY ON select  "TYPE", "FORMAT" from INFOOLDM1 READCOMMITTED where 1=2 SET FMTONLY OFF
 SET FMTONLY ON select  "TYPE", "FORMAT" from INFOOLDM1 READCOMMITTED where 1=2 SET FMTONLY OFF
(Yes there arre 2 of these, probably 1 from each servlet we run, as they exist from when the Application is started)   
 SET FMTONLY ON select  "COUNTER" from SCLDAPCONFIGM1 READCOMMITTED where 1=2 SET FMTONLY OFF

These transactions NEVER finish, and are open from when the system is rebooted (in this case 04:41 today) until it is shutdown

Is it possible to identify the SM processes that are holding these transactions open, and adjust the configuration so that these transactions terminate correctly, so we can truncate the SQL transaction log whilst the application is running?

If not we will be forced to
 1 Stop the application (Service Manager)
 2 Truncate the SQL transaction log
 3 Backup the dataabse
 4 Start the application (Service Manager)
Clearly we can't do that every hour!

  • You have to differentiate between log truncation and shrinking. Truncating the transaction log does not imply reducing the size of a physical file of this transaction log. Truncation marks portions of file as unused but does not shrink the file on filesystem. If you shrink the file while SM is stopped and setup regular backups and truncation then the transaction log will remain after a while at a certain size and should not grow unexpectedly to that enormous size as you have now.
  • Hello,

     

    You don't need to stop SM to shrink transaction log. You can do it online with a scheduled maintenance job (e.g. every night 11pm).

     

    Create a maintenace job which backups both data transaction log files and transaction log seperately and then shrinks transaction log.

     

    It's been long time since I've touched an SQL server but I've applied this solution to many customers and solved transaction log growing problems.

     

    BR.

  • Hi,

    We have recently upgraded to SM version 9.64 and are experiensing a similar problem:

    We can backup the SQL server transaction log file to disk but the transaction log file does not get flushed and continues to grow. As a result we need to expand the disk more and more.

    Before upgrade, in version 9.51, we never had this problem. We also then did a backup of the transaction log 4 times during the day and the transaction log file would then always get flushed. After that we could run the shrink command to reduce the transaction log to a normal size.

    Analyzing SQL Server we found a "running transaction" which seems to be the root cause for the transaction log file not get flushed at log backup time.

    Anyone know if there is a bug in SM version 9.64 which could cause this problem?