IBM Support

The Log file for the SQL database for iBase is too large (several Gb). How can I reduce its size ?

Troubleshooting


Problem

When using SQL Server with iBase, there are 2 SQL databases that are created : one for the data, and one for the Logs.
SQL Server then creates a Log file for each of these 2 databases.
This log file is an internal SQL Server file, and generally does not impact iBase.

But in some cases, the size of this log file grows up and up so much that it blocks SQL Server (for example because it's taking up all the disk space).

So, this document will explain how to reduce the size of this log file.

Symptom

iBase may take longer to respond, or you might have error messages telling you that there is no more free space on the disk.

Cause

By default, when SQL creates a database (whether it's an iBase DB or not), it creates a Log file for this database.
One option of this database is the Recovery Mode. Default is Full.
This mode adds up data in the log file, and if nothing is done, old data is never deleted. Which result in a log file that can be huge.


Environment

IBM i2 iBase with an SQL Server storage.

Diagnosing The Problem

In Windows, go to the folder where the database files are stored.
Search for files with an LDF extension, and check the size of the files.
If some of the files are quite large (over several hundred Gb), then you may want to reduce (or shrink) them.

Resolving The Problem

To shrink the Transaction log in SQL Server Management Studio -
  • Right-click the database, choose Properties, then Options.
  • Make sure "Recovery model" is set to "Simple", not "Full"
  • Click OK
  • Right-click the database again, choose Tasks -> Shrink files
  • Change file type to "Log"
  • Click OK.

Alternatively, the SQL to do it:
  • ALTER DATABASE mydatabase SET RECOVERY SIMPLE
  • DBCC SHRINKFILE (mydatabase_Log, 1)
(warning, in the first sql script, it's the main database. In the second script, it's the Log database)

Once these steps are done, check in the folder that the size has been reduced.

Related Information

How to shrink the transaction log

Document Location

Worldwide


[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSXW43","label":"i2 iBase"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB24","label":"Security Software"}}]

Document Information

More support for:
i2 iBase

Software version:
All Versions

Operating system(s):
Windows

Document number:
5693205

Modified date:
06 March 2020

UID

ibm15693205

Page Feedback