Core Issue
When the art_log.ldf file grows over 1 GB in size or consumes all available disk space on the Cisco CallManager server, the Call Detail Record (CDR) Analysis and Reporting (CAR) records no longer purge.
The CAR purge configuration requires CAR to purge a large number of rows (more than 500,000) from the Administrative Reporting Tool (ART) database. CAR currently attempts to delete all records in one large Structured Query Language (SQL) transaction. This causes the art_log.ldf transaction log to grow very large in size.
Resolution
These symptoms are addressed in Cisco bug ID CSCea55522. For a workaround, configure the CAR purge process to purge less than 500,000 records at any one point.
Review this information for a workaround:
These steps contain the output from a command prompt where steps are executed in order to shrink the ART transaction log. Do not simply copy and paste. Type what is typed here on the system when prompted.
In this case, the way that the CAR uses the ART database and attempts to bulk delete rows rather than an incremental delete or truncate causes the transaction log to grow to a huge size. The properties of the database show that a large amount of free space is available, over 1 GB.
- Execute this:
C:\>osql -E
1> use art
2> go
This purges the current closed transactions from the transaction log. This is normally done by the backup utility when it runs at night and backs up the ART database. If the ART database is not backed up or if a STIBack earlier than 3.5.18 is run, it exacerbates the large transaction log.
- Execute this:
1> backup log art with no_log
2> go
- Now, remove the free space from the database and the transaction log before the current size is documented:
1> dbcc shrinkdatabase (art)
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.
Note: The current size is equal to the number of 8 KB pages used, so start with 1768*8/1024=13.8MB for the database and 544*8/1024=4.25MB for the transaction log.
- The transaction log is still very large with very few used pages. This is because the current pointer into the transaction log is near the end of the contiguous log, so the unused space cannot be returned to the operating system (OS). It is necessary to fill the transaction log and force the pointer to wrap to the start of the log, which causes the transaction log to 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 art
4> while (@t <_ _="_" _100000br="_100000br">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
!--- "(1 row affected)" appears often.
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
-----------
100000
(1 row affected)
(1 row affected)
1> drop table test
2> go
1> checkpoint
2> go
- Try to shrink the database and transaction log again.
1> dbcc shrinkdatabase (art)
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.
- If there is still a large amount of free space, return to step four, and run through those again.
When they are done 100,000 at a time, there is limited success. It is recommended to set it to 900,000 and leave it for a while. This does not cause a CPU spike, this update takes around one percent CPU even on a 733Mhz processor.
- Repeat steps four and five and continue until the transaction log shrinks.
Refer to these documents for more information:
