From NippAero

Information

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

Problem

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.

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_tech_note09186a008049aab0.shtml

Action Plan

We need to shrink the CDR_log.ldf. Please DO NOT delete the file as it will cause the system down.

Below are the procedures:

shrink cdr transaction log:

For this example type what I typed below and follow along on your system.

In this case, the way CAR uses the ART database and attempts to bulk delete // rows instead of using incremental delete or truncate has caused the transaction log to grow to a huge size. Checking the properties of the database shows that it has a large amount (1GB+) of free space.

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

This purges current closed transactions from the transaction log. This is normally done by the backup utility when it runs at night and backs up the CDR database. If you are not backing up the CDR database or are running STIBack earlier than 3.5.18, then this exacerbates the large transaction log.

1> backup log cdr with no_log
2> go

Now remove free space from database and transaction log before beginning to document current size.

 1> dbcc shrinkdatabase (cdr)
 2> go
 DbId   FileId CurrentSize MinimumSize UsedPages   EstimatedPages
 ------ ------ ----------- ----------- ----------- --------------
     13      1         368          96         360            360
     13      2          96          63          96             56

(2 rows affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The current size = number of 8KB pages used, so we start at 1768*8/1024=13.8MB for the database // and 544*8/1024=4.25MB for the transaction log

Now the problem on the system is that the transaction log is still very large with very few used pages, this is becuase the current pointer into the transaction log is near the end of the contiguous log so the unused space cannot be returned the OS. We have to fill up the transaction log and force the pointer to wrap back around to the beginning of the log, then the transaction log will shrink.

1> create table Test (test int DEFAULT NULL NULL)
2> go
1> insert into Test (test) VALUES (1)
2> go
(1 row affected)
1> declare @t integer
2> set @t = 0
3> use cdr
4> while (@t <= 100000)
5>     BEGIN
6>     update Test set test=1 where test=1
7>     select @t = @t + 1
8>     if ((@t % 500) = 0)
9>         BEGIN
10>        select @t
11>        END
12>    END
13> go

You will see a lot of "(1 row affected)"

(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
etc...

Then:

1> drop table test
2> go
1> checkpoint
2> go

Now try to shink the database and transaction log again

1> dbcc shrinkdatabase (cdr)
2> go
 DbId   FileId CurrentSize MinimumSize UsedPages   EstimatedPages
 ------ ------ ----------- ----------- ----------- --------------
     13      1         368          96         360            360
     13      2          96          63          96             56

(1 row affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now if you still have a large amount of free space go back to "1> create table Test (test int DEFAULT NULL NULL)" and run through those again. I usually don't have much luck going 100000 at a time, so I usually set it 900000 and go to lunch and come back. This will not cause a CPU spike, this update takes ~1% CPU even on the 733Mhz processor I'm working on. repeat everything between create table......dbcc shrinkdatabase (cdr) go until the transaction log shrinks.

Good luck!