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

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

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. 

Environment

Release: All Releases

Resolution

• 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:

• Backup Under the Full Recovery Model
  http://msdn.microsoft.com/en-us/library/ms190217%28SQL.90%29.aspx
   
• Backup Under the Simple Recovery Model
  http://msdn.microsoft.com/en-us/library/ms191164%28SQL.90%29.aspx
   
• Considerations for Switching from the Simple Recovery Model
  http://msdn.microsoft.com/en-us/library/ms178052.aspx
   
• How to: Back Up a Transaction Log
  http://msdn.microsoft.com/en-us/library/ms179478.aspx
   
• Working with Transaction Log Backups
  http://msdn.microsoft.com/en-us/library/ms190440.aspx
   
• Backup Log with Truncate_Only: Like a Bear Trap
  http://sqlserverpedia.com/blog/sql-server-backup-and-restore/backup-log-with-truncate_only-like-a-bear-trap/
   
• Shrinking Databases
  http://sqlserverpedia.com/wiki/Shrinking_Databases