Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
New Member

Deleting and Backing up CDRs issue

Hello,

i have CM 3.1(4b) and the disk is nearly full with CDR records. I know that there are two ways to delete them (query analyzer) or reduce the number (1500000 cdr's default) of CDRs kept in the system.

1) Is there any way to keep only the last , for example, 4 days CDRs?

2) Is there any reference on how to to backup whole CDR database before deleting the CDRs through query analyzer?

Thanks

6 REPLIES
New Member

Re: Deleting and Backing up CDRs issue

Hello, step 2 has been solved but i am still wondering how to keep always the last 4 days CDR (only)

Any ideas?

Bronze

Re: Deleting and Backing up CDRs issue

First, you need to convert the day/time into Unix Epoch time:

http://www.onlineconversion.com/unix_time.htm

This will give you a string that measures the number of seconds since 1/1/1970.

Now, run this query:

DELETE FROM CallDetailRecordDiagnostic

WHERE callIdentifier IN (SELECT cdr.dbo.CallDetailRecord.origLegCallIdentifier

FROM cdr.dbo.CallDetailRecord

WHERE cdr.dbo.CallDetailRecord.dateTimeDisconnect < ? UNION SELECT cdr.dbo.CallDetailRecord.destLegIdentifier

FROM cdr.dbo.CallDetailRecord WHERE cdr.dbo.CallDetailRecord.dateTimeDisconnect < ?)

DELETE FROM cdr.dbo.CallDetailRecord WHERE cdr.dbo.CallDetailRecord.dateTimeDisconnect < ?

Replace the ?'s with the time string you generated

New Member

Re: Deleting and Backing up CDRs issue

Thanks, i'll try to do that.

With this way customer has to remove manually the CDRs every -e.g. 8 days-.

Is there are automated procedure for removing the CDRs? (Run it once and never touch it again while it keeps 4 days only CDRs).

New Member

Re: Deleting and Backing up CDRs issue

Hello again

i tried parsing the following string in order to keep only today's CDRs with a readable time format but an error appeared. The string i parsed (parse query at query analyzer) was:

DELETE FROM CallDetailRecordDiagnostic

WHERE callIdentifier IN (SELECT cdr.dbo.CallDetailRecord.origLegCallIdentifier)

FROM cdr.dbo.CallDetailRecord

WHERE cdr.dbo.CallDetailRecord.dateTimeDisconnect < 1066262400 UNION SELECT cdr.dbo.CallDetailRecord.destLegIdentifier

FROM cdr.dbo.CallDetailRecord WHERE cdr.dbo.CallDetailRecord.dateTimeDisconnect < 1066262400)

DELETE FROM cdr.dbo.CallDetailRecord WHERE cdr.dbo.CallDetailRecord.dateTimeDisconnect < 1066262400

-------------

1066262400 corresponds to today's date.

The error that appeared was:

Server: Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'FROM'.

Any idea?

Thanks

Bronze

Re: Deleting and Backing up CDRs issue

Did you choose the CDR database in the pulldown list at the top of query analyzer?

Try removing the cdr.dbo from the beginning of each string.

New Member

Re: Deleting and Backing up CDRs issue

Hello dugrant,

thanks very much. Query was parsed and everything is o.k.

Kind regards

228
Views
5
Helpful
6
Replies
作成コンテンツを作成するには してください