This document explains how to remove/purge Call Detail Records (CDRs)
from Cisco CallManager's Structured Query Language (SQL) database without the
use of the Administrative Reporting Tool (ART).
For information on how to purge the CDRs from Cisco CallManager's SQL
database using the ART, refer to
System Configuration-Using Manual Database Purge.
With CDR, the Cisco CallManager user receives this error message or
The number of rows in CallDetailRecord table in the
CDR database has crossed the threshold limit
This error message appears when the database has reached the set limit
of records and needs to be purged.
Cisco recommends that you have knowledge of these topics:
The information in this document is based on Cisco CallManager 3.x and
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.
Technical Tips Conventions for more information on document
Complete these steps in order to reduce CDR thershold limits alert
emails or error messages:
Increase the max number of rows in the Billing Table of the CDR in
order to reduce the alert.
This can be done from the Cisco CallManager Administration web
page. In CAR > System >
Database > CAR Database Alert >
Max number of rows in Billing Table*, you can change that
value to a higher one (i.e. 2,500,000).
Use the auto-purge mechanism in order to avoid the thershold limits
alert. In order to configure automatic purge, choose CAR >
System > Database > Configure Automatic
Database Purge. You can enable the CAR purge for records older than a certain
number of days you want to keep.
This method is very processor-intensive, and should not be done during
normal business hours.
Select Start > Programs > Microsoft SQL Server 7.0
> Query Analyzer.
Note: In Cisco CallManager 3.3 and later, Microsoft SQL Server 2000 is
used. Select Start > Programs > Microsoft SQL Server > Query
Analyzer in Cisco CallManager 3.3 and later.
Select Use Local > Windows NT authentication/SQL Server
authentication depending on the version of Cisco CallManager. Click
Note: For Cisco CallManager 3.3, select SQL Server
authentication and enter your username and password. For Cisco
CallManager 4.x, select Windows NT authentication. Windows NT
Authentication is recommended, although the system supports SQL Authentication.
Setting Cisco CallManager for mixed mode authentication in release 4.0 and
later is not supported. Cisco CallManager 4.x servers upgraded from the earlier
versions fail with SQL Server authentication and the system needs to be changed
back to Windows NT authentication. Refer to
Unable to Log into SQL Query Analyzer After Upgrading from Cisco CallManager
3.x to 4.x for more information.
Select the CDR database that contains all of the records you wish
Issue the DELETE from Calldetailrecord
If your CDR database contains a lot of records, this step might
take awhile. When this operation completes, this message appears at the bottom
of the window:
Query batch completed.
This image shows both the command and the system
Issue the Delete from
CallDetailRecordDiagnostic command to delete all of the records
in the CallDetailRecordDiagnostic table as well. This message appears at the
bottom of the window:
Query batch completed.
In order to use the Cisco CallManager web page, you must stop and start
the Database Layer Monitor service and you need to schedule downtime to do it.
Manual deletion of the CDR through the Cisco CallManager web page is not as
processor-intensive as the Delete CDRs Through the
Query Analyzer procedure.
Select Service > Service Parameters from the
main administration web page.
The Service Parameters Configuration page opens.
Select your server, and select the Database Layer Monitor
The default value for Max CDR Records is
Change the value in the Max CDR Records field to a reduced value
(for example, 1400000) and click Update.
Note: Change the value for Max CDR Records incrementally. If you reduce
it to 0 in a single step, you could cause a CPU spike.
Select Application > Cisco CallManager
Select Tools > Control Center from the Cisco
CallManager Serviceability page.
The Control Center page opens.
Find the Service Control for the Database Layer Monitor service,
and click Stop.
After the service stops, as indicated by the Service Status
indicator (shown in the preceding image), click Start to
restart the service.
When the service restarts, the CDR contains the new number of CDRs
as designated by the CDR Max Records value. In this example, the new number is
Repeat steps 2 through 7. Reduce the number in the Max CDR Records
field each time until the value is 0.
Once you have reached 0 records, issue this command to check the
result in the CDR database through the SQL Server Query Analyzer:
select * from CallDetailRecord
Note: After you finish this procedure, return the value of Max CDR
Records to its default setting, as this image shows. Otherwise, the system does
not retain any CDRs.