Clarity: The MSSQL transaction log for the database is full, or has grown excessively.

Document ID : KB000052153
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

You are using Microsoft SQL as database engine, and you're experiencing one of the following.

  1. Certain operations, such as the Datamart Extraction Job are failing, and you see the following error in the app-niku.log:

    SQL error code: 9002
    Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]The transaction log for database 'ClarityDatabase'is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

    Please note that ClarityDatabase will be the name of your Clarity Database.

    This is occurring because the transaction log for the Clarity database is full, and no more SQL transactions can be processed.

  2. The transaction log file(s) from the Clarity database have grown excessively.
    This is because the transaction log is set with unrestricted auto-growth.

This is not a Clarity problem, but a database one.

A way to workaround this is to shrink the transaction log.

The query below will drop the existing Transaction log.
After doing this, do a full backup (since point-in-time rollbacks will not be possible), and shrink the log file to regain disk space.

After shrinking the log file, you can set the Recovery model to Simple, and also set the new size limit for the log file.
If Auto-growth is enabled, the Log file will grow up to that limit. Once the limit is reached, it will overwrite itself.

Considerations to have about Simple mode:

When you switch over to Simple mode, you lose what it's called 'point-in-time' restore.
You can only go back to the latest full backup.

This is really up to your DBA's consideration, and backup/recovery schema for the affected environment.
In Full mode, you'd receive an error that the transaction log is full, and the operation could not be completed.

Further reading:

Solution:

How to shrink the Transaction Log:

  1. Stop ALL Clarity services.

  2. Log into SQL Server Management Studio, and run the following query against the Clarity database.
    BACKUP LOG [DatabaseName] WITH TRUNCATE_ONLY

    Where DatabaseName is the name of the Clarity database.

  3. Right-click the Clarity database -> Tasks... -> Shrink files.

    1. Choose Log File.

    2. Release unused space.

    3. Click OK.

  4. Do a full database backup (optional, but recommended).

  5. Start Clarity services.

Keywords: CLARITYKB, db, microsoft sql server.