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

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


Article ID: 52153


Updated On:


Clarity PPM On Premise Clarity PPM SaaS


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

  1. Certain operations, such as jobs are failing, and you see the following error in the app or bg-ca.log:

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

    This is occurring because the transaction log for the Clarity (NIKU) database is full, and no more SQL transactions can be processed.
  2. The transaction log file(s) .ldf from the Clarity database have grown excessively. This is because the transaction log is set with unrestricted auto-growth.

  3. Possible error while upgrading.  The install.log will have the error below

           (ExecTask) java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver][SQLServer]The server failed to resume the transaction. 


Release: All Releases


  • This is not a Clarity problem, but a database one.
  • Besides increasing the space in the logs drive, 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.

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  // DBCC SHRINKFILE (2, 1); -- here 2 is the file ID for transaction log file (SQL 2008 and higher)
  3. Where DatabaseName is the name of the Clarity database.
  4. Right-click the Clarity database -> Tasks... -> Shrink files.
    • Choose Log File.
    • Release unused space.
    • Click OK.
  5. Do a full database backup (optional, but recommended).
  6. Start Clarity services.

Additional Information

Further reading: