Highlighted
Absent Member.
Absent Member.
941 views

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!

0 Likes
3 Replies
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Service Manager SQL Transaction log grows too large

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.
0 Likes
Highlighted
Honored Contributor.. Honored Contributor..
Honored Contributor..

Re: Service Manager SQL Transaction log grows too large

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.

0 Likes
Highlighted
Super Contributor.. Super Contributor..
Super Contributor..

Re: Service Manager SQL Transaction log grows too large

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?

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.