Guest

Cisco Unified Communications Manager (CallManager)

Shrink the CDR/CAR Database Size

Cisco - Shrink the CDR/CAR Database Size

Document ID: 65119

Updated: Mar 26, 2007

   Print

Introduction

This document provides information that you can use to shrink the Call Details Record /CDR Analysis and Reporting (CDR/CAR) database due to defects.

Cisco bug IDs CSCea55522 (registered customers only) and CSCeb47037 (registered customers only) )—art_log.ldf file grows very large, CAR records might stop purging. Both the art_log.ldf and cdr_log.ldf file have been known to grow very large. This causes low disk space and results in slow Cisco CallManager performance and/or services are affected.

Prerequisites

Requirements

Ensure that you meet these requirements before you attempt this configuration:

  • Cisco CallManager 3.x and 4.x administration

  • SQL database management

Components Used

The information in this document is based on Cisco CallManager 3.x.

The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.

Conventions

Refer to Cisco Technical Tips Conventions for more information on document conventions.

Problem

Cisco CallManager returns this SQL error message:

Error No. -2147219942 (0x8004061a): CDBLException Dump:
	 [Unknown SQL driver error. Please send message to Cisco developer for Telephony
	 Database Layer] 

The reason for this error is that SQL transaction log files for some databases grow over 1 GB in size. The SQL transaction log files are the *.ldf files located in the C:\Program Files\Microsoft SQL Server\MSSQL\Data directory. The *.mdf files, especially art.mdf and cdr.mdf, might be larger that 1 GB depending on system configuration. These solutions only target *.ldf files greater than 1 GB in size.

Note: Ensure that there are no MGCP devices in your network attempting to register with Cisco CallManager that are not registered. These devices continue to attempt to register indefinitely and can cause many megabytes in logs to be recorded.

Solutions

Use the solutions in this section to solve the problem.

Solution 1

The most common problem is that the Backup and Restore System (BARS) is not configured, or is configured but failing. Make sure backups are set on a regular basis. During backups, BARS truncates the CDR transaction log. Go to C:\Program Files\Common Files\Cisco\Log\BARS to review the BARS backup successful or failed log.

If BARS is truncating and the log and failures occur in the logs, go to Solution 2.

Solution 2

Shrink the database via the SQL Enterprise Manager.

Note: This is done on the Publisher.

  1. Select Start > Programs > MS SQL Server > Enterprise Manager.

  2. Choose the Publisher server.

  3. You can either go to the ART database or CDR database, based on which file is large.

  4. Right-click the database.

  5. Select All Tasks > Shrink database and click Files.

  6. Select the database file CDR and CDR_log on the new window.

  7. Shrink each of them.

    This process takes some time.

    If the file is still large or SQL is not able to shrink the transactional file, use Solution 3.

Solution 3

For SQL 2000 (Cisco CallManager 3.3 and 4.0), complete these steps to reduce the size of the CDR_log.LDF. This is located at C:\Program Files\Microsoft SQL Server\MSSQL\Data.

Use these commands at the command prompt:

ART
C:\>osql -E
1>use art
2>go

1>backup log art with no_log
2>go

1>dbcc shrinkdatabase (art)
2>go

CDR
C:\>osql -E
1>use cdr
2>go

1>backup log cdr with no_log
2>go

1>dbcc shrinkdatabase (cdr)
2>go

Refer to INF: How to Shrink the SQL Server 7.0 Transaction Log leavingcisco.com for more information on the Microsoft shrinking database.

Alternate Solutions

This section provides three alternate solutions:

  1. You can configure the CAR purge process to purge less than 500,000 records at any one point in time. Refer to CAR System Configuration.

  2. You can manually delete CDRs without the Administrative Reporting Tool (ART). Refer to Cisco CallManager: Manually Deleting Call Detail Records (CDRs) Without the Administrative Reporting Tool (ART).

  3. You can restrict the number of CDR records that go into the database:

    Select CCMadmin page Select Service > Service Parameters > Select the according server > Cisco Database Layer Monitor Service > Max CDR records.

    It is recommended that you perform this task after hours. The reduction in the number of CDR records causes the deletion process, and the CPU increases. You can start with increments of 50,000 records. Afterwards, cycle the database layer monitor service.

Refer to CallManager Issue Resolution with CDR and ART FAQ to view CDR and CAR FAQs.

Related Information

Updated: Mar 26, 2007
Document ID: 65119