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 CAR System Configuration-Using Manual Database Purge.
With CDR, the Cisco CallManager user receives this error message or e-mail alert:
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:
Cisco CallManager Administration
SQL database management
The information in this document is based on Cisco CallManager 3.x and 4.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.
Refer to Cisco Technical Tips Conventions for more information on document conventions.
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 OK.
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 User 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 to delete.
Issue the DELETE from Calldetailrecord command.
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 message:
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 service.
The default value for Max CDR Records is 1500000:
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 Serviceability.
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 1400000.
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.
The Cisco Support Community is a forum for you to ask and answer questions, share suggestions, and collaborate with your peers.
Refer to Cisco Technical Tips Conventions for information on conventions used in this document.