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

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• Clarity PPM On Premise

Issue/Introduction

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.
3. Receive 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
Component: CA PPM Environmental

Resolution

This is not a PPM 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 PPM database.
   BACKUP LOG [DatabaseName] WITH TRUNCATE_ONLY (SQL 2005) // 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