The transaction log (mdb_log.ldf) is very large. What can I do to reduce it's size and control how quickly it grows in the future?

Document ID : KB000025484
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:

All Supported versions of MSSQL Server for use with Client Automation mdb

CA Client Automation 12.8 , 12.9

 

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 Query analyzer select mdb from the database drop down window and run the following queries*:

    • BACKUP LOG mdb with TRUNCATE_ONLY
    • DBCC SHRINKFILE(mdb_log, 100)


    In this procedure the transaction log is reduced first and then it is shrunk to 100MB (or less).

    *For SQL 2000/2005 you can also just run the following query without selecting the MDB explicitly:

    USE MDB
    DBCC SHRINKFILE(mdb_log, 2)
    BACKUP LOG mdb WITH TRUNCATE_ONLY


  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:

    SQL Enterprise Manager -> MDB -> properties -> Options -> Recovery**

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

    **You can do this via the same procedure using SQL Server Management Studio in SQL 2005

    We would also recommend your SQL Server DBA set up a maintenance plan for the MDB database.
    This can be done in the SQL Server Enterprise Manager.
    This option can be found by drilling down to "Microsoft SQL Servers -> SQL Server Group
    -> <machine name> -> Management -> Database 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 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