The transaction log of mdb (mdb_log.ldf) is very large

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

Symptoms:

  • DSM-MDB server has run out of disk space
  • Disk space low due to MDB transaction log (mdb_log.ldf) size
  • DSM console fails to open due to database connectivity
  • DSM hangs and stops working
  • To avoid the rapid growth of the transaction log

 

Cause:

The above symptoms can be caused by the lack of SQL maintenance on the mdb database. The document will step you through how to resolve these issues and avoid them in the future.

 

Environment:

Versions of MSSQL Server 2008 , R2 , 2012 for use with Client Automation mdb

CA Client Automation 12.8 , 12.9 , 14.0

 

Resolution:

Please find details for the MDB maintenance.

If the transaction log (mdb_log.ldf) is very large you can run the following MS SQL Server procedure.

  1. Stop the CAF services using
    'CAF stop' command line

  2. In the MS SQL Server Management Studio, create a new query, select mdb from the database drop down window and run the following queries*:

    • BACKUP LOG mdb TO DISK = 'NUL:' 
    • DBCC SHRINKFILE(mdb_log, 100)


    In this procedure the transaction log is first truncatede and then its file is shrunk to 100MB (or less).


  3. Start the CAF service using 
    'CAF start'

    To avoid the transaction log increasing in size so rapidly, check the MDB database model. 
    If it is set to full, you might like to consider changing it to simple:

    To check how this option was set, go to:

    Microsoft SQL Server Management Studio -> MDB -> properties -> Options -> Recovery model :

    • For Simple Recovery model it means the model allows the database to be recovered to the most recent backup.
    • Full Recovery allows the database to be recovered to the point of failure


    We recommend SIMPLE to avoid the problem of transaction log filling up very quickly.

    We would also recommend your SQL Server DBA set up a maintenance plan for the MDB database. 
    This can be done in the Microsoft SQL Server Management Studio : 
    This option can be found by drilling down to "Management -> Maintenance Plans" -> Right-click and choose "New Maintenance Plan" and the Database Maintenance Wizard will open up. The settings found here depend on your preferences. 

    Note: Before any modification, complete database backup is mandatory.

 

Additional Information:

For Mssql lower than 2008 please uses the docID: TEC476219

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec476219.aspx?intcmp=searchresultclick&resultnum=1

 

For more details on proper SQL maintenance please see the document entitled "Proper Care and Feeding of your SQL MDB (Recommendations for General MDB Maintenance) " 

https://support.ca.com/phpdocs/0/common/impcd/r11/MDBMain/Doc/MDB03_Proper%20Care%20and%20Feeding%20of%20your%20SQL%20MDB.pdf