How to Use ViciDial Admin Utilities — Archive, Cleanup & Maintenance

Master production ViciDial database maintenance, call log archival, and system cleanup to optimize performance and prevent disk space exhaustion.

Introduction

ViciDial deployments accumulate data quickly. A moderately active call center logs millions of records monthly across vicidial_log, vicidial_closer_log, vicidial_carrier_log, and related tables. Without systematic archival and cleanup, your MySQL database bloats, queries slow, backups consume excessive storage, and the web interface becomes unresponsive.

This tutorial covers the actual administrative utilities shipped with ViciDial, real-world maintenance strategies, and the exact commands your production environment needs. Whether you're running a 10-agent shop or a 500-seat operation, improper maintenance will eventually force an emergency cleanup window that disrupts service.

Prerequisites

Before proceeding, you need:

• ViciDial installed and running on Linux (Debian/CentOS tested)
• Root or sudo access to the server
• MySQL/MariaDB admin credentials with access to the asterisk database
• Cron capability to schedule regular maintenance tasks
• Backup strategy already in place (test your backups before archiving)
• Shell access to /usr/share/astguiclient/ (standard ViciDial CLI location)
• Understanding of your data retention policy (how long to keep records on-disk vs. archived)

Understanding ViciDial Data Architecture

ViciDial stores call records in distinct tables, each serving different purposes:

Understand your call volume before designing a maintenance schedule. A 500-seat operation might generate 500K calls/day; a 10-seat operation 10K/day.

Section 1: Built-In Archive Utilities

Using ARCHIVE_MANAGER.pl

ViciDial ships with ARCHIVE_MANAGER.pl, a Perl script designed to move old call records to archive tables. This is the official, supported method for archival.

Location: /usr/share/astguiclient/ARCHIVE_MANAGER.pl

What it does:

• Copies records older than a configurable threshold to _archive tables
• Optionally deletes original records after verification
• Generates detailed logs of what was archived
• Respects foreign key constraints and referential integrity

Basic syntax:

/usr/share/astguiclient/ARCHIVE_MANAGER.pl \
  --archiveolderthandays 180 \
  --deletearchived 1 \
  --logfile /var/log/vicidial/archive.log

Parameters explained:

• --archiveolderthandays N: Archive records older than N days (required)
• --deletearchived 1|0: Delete original records after archiving (1=yes, 0=no)
• --logfile PATH: Write operation log here (creates directory if needed)
• --database asterisk: Specify database name (default: asterisk)
• --host localhost: Database host (default: localhost)
• --user vicidialuser: Database user (required for auth)
• --pass PASSWORD: Database password (security risk; prefer .my.cnf)

Real-world example with .my.cnf:

Create /root/.my.cnf with restricted permissions:

[client]
user=vicidialuser
password=your_secure_password
host=localhost

Set permissions:

chmod 600 /root/.my.cnf

Then run without exposing password:

/usr/share/astguiclient/ARCHIVE_MANAGER.pl \
  --archiveolderthandays 180 \
  --deletearchived 1 \
  --logfile /var/log/vicidial/archive.log

Understanding Archive Table Creation

When you first run ARCHIVE_MANAGER.pl, it creates _archive tables automatically:

CREATE TABLE vicidial_log_archive LIKE vicidial_log;
CREATE TABLE vicidial_closer_log_archive LIKE vicidial_closer_log;

Verify archive tables exist:

mysql -u vicidialuser -p asterisk -e "SHOW TABLES LIKE '%archive%';"

Expected output:

+--------------------------------------+
| Tables_in_asterisk (%archive%)       |
+--------------------------------------+
| vicidial_carrier_log_archive         |
| vicidial_closer_log_archive          |
| vicidial_list_log_archive            |
| vicidial_log_archive                 |
+--------------------------------------+

Archival Monitoring & Verification

After running archival, check the log file:

tail -100 /var/log/vicidial/archive.log

Look for:

2024-01-15 02:30:45 - Starting ARCHIVE_MANAGER
2024-01-15 02:31:12 - vicidial_log: Copying 1,245,000 records dated before 2023-08-17
2024-01-15 02:32:40 - vicidial_log: Deleting 1,245,000 archived records
2024-01-15 02:33:01 - vicidial_closer_log: Copying 987,000 records dated before 2023-08-17
2024-01-15 02:34:15 - vicidial_closer_log: Deleting 987,000 archived records
2024-01-15 02:35:00 - Archive complete: 3.2M records archived

Verify archive count matches original deletion:

mysql -u vicidialuser -p asterisk -e \
  "SELECT COUNT(*) as 'vicidial_log_archive' FROM vicidial_log_archive;"

Section 2: Database Cleanup Operations

Truncating Inactive Campaign Logs

Campaign logs can accumulate quickly with many short-duration campaigns. Archive first, then truncate if safe:

# Archive 90-day-old campaign logs
/usr/share/astguiclient/ARCHIVE_MANAGER.pl \
  --archiveolderthandays 90 \
  --deletearchived 1

# Then safely truncate extremely old archived logs (optional backup first)
mysqldump -u vicidialuser -p asterisk vicidial_campaign_log_archive > \
  /backup/campaign_archive_$(date +%Y%m%d).sql

Clearing Temporary & Log Tables

ViciDial creates temporary tables during operation. These don't need long-term retention:

mysql -u vicidialuser -p asterisk << 'EOF'
-- Delete old queue logs (safe to clear, not critical)
DELETE FROM vicidial_api_log WHERE call_date < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Optimize tables after large deletions
OPTIMIZE TABLE vicidial_log;
OPTIMIZE TABLE vicidial_closer_log;
OPTIMIZE TABLE vicidial_carrier_log;
EOF

Removing Orphaned Records

Sometimes records remain after campaigns end. Find and remove them safely:

-- Find vicidial_log entries without matching campaigns (read-only check first)
SELECT COUNT(*) FROM vicidial_log vl
WHERE NOT EXISTS (
  SELECT 1 FROM vicidial_campaigns vc 
  WHERE vc.campaign_id = vl.campaign_id
) AND vl.call_date < DATE_SUB(NOW(), INTERVAL 180 DAY);

-- If count is acceptable, delete (with caution!)
DELETE FROM vicidial_log WHERE NOT EXISTS (
  SELECT 1 FROM vicidial_campaigns vc 
  WHERE vc.campaign_id = vicidial_log.campaign_id
) AND vicidial_log.call_date < DATE_SUB(NOW(), INTERVAL 180 DAY);

Removing Test Records

Development testing leaves junk records. Clean them with:

mysql -u vicidialuser -p asterisk << 'EOF'
-- Remove test campaigns and their logs
DELETE vl FROM vicidial_log vl
INNER JOIN vicidial_campaigns vc ON vl.campaign_id = vc.campaign_id
WHERE vc.campaign_name LIKE 'TEST_%'
  AND vl.call_date < DATE_SUB(NOW(), INTERVAL 30 DAY);

DELETE vcl FROM vicidial_closer_log vcl
INNER JOIN vicidial_campaigns vc ON vcl.campaign_id = vc.campaign_id
WHERE vc.campaign_name LIKE 'TEST_%'
  AND vcl.call_date < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Delete orphaned test campaigns
DELETE FROM vicidial_campaigns WHERE campaign_name LIKE 'TEST_%';
EOF

Section 3: Performance Optimization After Cleanup

After major deletions or archival, optimize the database structure:

Table Optimization

mysql -u vicidialuser -p asterisk << 'EOF'
-- Optimize all main tables after archival
OPTIMIZE TABLE vicidial_log;
OPTIMIZE TABLE vicidial_closer_log;
OPTIMIZE TABLE vicidial_carrier_log;
OPTIMIZE TABLE vicidial_campaign_log;
OPTIMIZE TABLE vicidial_list_log;
OPTIMIZE TABLE vicidial_statistic_log;

-- Check table integrity
CHECK TABLE vicidial_log;
CHECK TABLE vicidial_closer_log;
EOF

Note: OPTIMIZE TABLE locks the table briefly. Schedule during maintenance windows or use OPTIMIZE TABLE ... LOCK=NONE (MySQL 8.0+).

Rebuilding Indexes

After major deletes, indexes may become fragmented:

# Rebuild primary indexes on key tables
mysql -u vicidialuser -p asterisk << 'EOF'
-- Recreate indexes for faster queries post-cleanup
ALTER TABLE vicidial_log ENGINE=InnoDB;
ALTER TABLE vicidial_closer_log ENGINE=InnoDB;
ALTER TABLE vicidial_carrier_log ENGINE=InnoDB;
EOF

Verifying Table Status

# Check how much space you reclaimed
mysql -u vicidialuser -p asterisk << 'EOF'
SELECT 
  TABLE_NAME,
  ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024), 2) as size_gb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'asterisk'
  AND TABLE_NAME IN ('vicidial_log', 'vicidial_closer_log', 'vicidial_carrier_log')
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;
EOF

Sample output:

+---------------------+---------+
| TABLE_NAME          | size_gb |
+---------------------+---------+
| vicidial_log        | 45.23   |
| vicidial_closer_log | 38.91   |
| vicidial_carrier_log| 12.34   |
+---------------------+---------+

Section 4: Scheduling Automated Maintenance

Creating a Maintenance Cron Job

Create /usr/local/bin/vicidial-maintenance.sh:

#!/bin/bash

# ViciDial Automated Maintenance Script
# Run daily at 2 AM via cron

LOG_DIR="/var/log/vicidial"
ARCHIVE_LOG="$LOG_DIR/archive_$(date +%Y%m%d_%H%M%S).log"
MAINT_LOG="$LOG_DIR/maintenance_$(date +%Y%m%d_%H%M%S).log"

mkdir -p "$LOG_DIR"

# Function to log messages
log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$MAINT_LOG"
}

log_message "=== ViciDial Maintenance Started ==="

# Step 1: Archive old records (older than 180 days)
log_message "Starting archival of records older than 180 days..."
/usr/share/astguiclient/ARCHIVE_MANAGER.pl \
  --archiveolderthandays 180 \
  --deletearchived 1 \
  --logfile "$ARCHIVE_LOG" >> "$MAINT_LOG" 2>&1

if [ $? -eq 0 ]; then
    log_message "Archival completed successfully"
else
    log_message "WARNING: Archival returned non-zero exit code"
fi

# Step 2: Clean up API logs older than 30 days
log_message "Cleaning API logs older than 30 days..."
mysql -u vicidialuser -p"$(cat /root/.mysql_pass)" asterisk << 'SQLEOF' >> "$MAINT_LOG" 2>&1
DELETE FROM vicidial_api_log WHERE call_date < DATE_SUB(NOW(), INTERVAL 30 DAY);
DELETE FROM vicidial_log_archive WHERE call_date < DATE_SUB(NOW(), INTERVAL 365 DAY);
SQLEOF

log_message "API log cleanup completed"

# Step 3: Optimize tables
log_message "Optimizing tables..."
mysql -u vicidialuser -p"$(cat /root/.mysql_pass)" asterisk << 'SQLEOF' >> "$MAINT_LOG" 2>&1
OPTIMIZE TABLE vicidial_log;
OPTIMIZE TABLE vicidial_closer_log;
OPTIMIZE TABLE vicidial_carrier_log;
SQLEOF

log_message "Table optimization completed"

# Step 4: Check database integrity
log_message "Running database integrity check..."
mysql -u vicidialuser -p"$(cat /root/.mysql_pass)" asterisk << 'SQLEOF' >> "$MAINT_LOG" 2>&1
CHECK TABLE vicidial_log;
CHECK TABLE vicidial_closer_log;
SQLEOF

log_message "=== ViciDial Maintenance Completed ==="

Make it executable:

chmod 755 /usr/local/bin/vicidial-maintenance.sh

Add to Crontab

# Edit root crontab
crontab -e

Add:

# Run ViciDial maintenance daily at 2 AM (off-hours)
0 2 * * * /usr/local/bin/vicidial-maintenance.sh

Alternative: Weekly schedule:

# Run weekly on Sunday at 2 AM for larger operations
0 2 * * 0 /usr/local/bin/vicidial-maintenance.sh

Monitoring Cron Execution

Check if cron job ran successfully:

grep vicidial-maintenance /var/log/syslog
# or
grep vicidial-maintenance /var/log/cron

Review maintenance logs:

ls -lh /var/log/vicidial/maintenance_*.log
tail -50 /var/log/vicidial/maintenance_*.log | less

Section 5: Manual Admin Utilities Reference

Using astguiclient Command-Line Tools

ViciDial ships with several command-line utilities in /usr/share/astguiclient/:

get_asterisk_status.pl

Check overall Asterisk health:

/usr/share/astguiclient/get_asterisk_status.pl

Output:

Asterisk Version: 18.15.0
Uptime: 45 days, 12:34:56
Active Calls: 23
Channels: 156

get_call_records.pl

Export call records for external analysis:

/usr/share/astguiclient/get_call_records.pl \
  --start-date "2024-01-01" \
  --end-date "2024-01-31" \
  --campaign "CAMPAIGN_001" \
  --output-file /tmp/jan_records.csv

Database Backup & Restore

Backup the entire asterisk database:

mysqldump -u vicidialuser -p asterisk > \
  /backup/asterisk_$(date +%Y%m%d_%H%M%S).sql

Backup only vicidial_log:

mysqldump -u vicidialuser -p asterisk vicidial_log > \
  /backup/vicidial_log_$(date +%Y%m%d).sql.gz

Restore from backup:

mysql -u vicidialuser -p asterisk < /backup/asterisk_20240115_020000.sql

Section 6: Performance Monitoring & Capacity Planning

Monitor Disk Space

# Check MySQL data directory
du -sh /var/lib/mysql/asterisk/

# Check overall filesystem
df -h /var/lib/mysql/

Expected output:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1       500G  450G   50G  90% /

Alert threshold: When database exceeds 85% of available space, schedule immediate archival.

Query Performance Baseline

Before and after maintenance, establish performance metrics:

mysql -u vicidialuser -p asterisk << 'EOF'
-- Query latency check
SELECT 
  DATE_FORMAT(call_date, '%Y-%m-%d') as date,
  COUNT(*) as record_count
FROM vicidial_log
GROUP BY DATE_FORMAT(call_date, '%Y-%m-%d')
ORDER BY call_date DESC
LIMIT 30;
EOF

Compare query execution time before/after archival using:

mysql -u vicidialuser -p asterisk -v -v << 'EOF'
SELECT COUNT(*) FROM vicidial_log WHERE call_date > DATE_SUB(NOW(), INTERVAL 30 DAY);
EOF

Database Size Trend

Create a monitoring script that tracks growth:

#!/bin/bash

MONITOR_LOG="/var/log/vicidial/db_size_trend.log"

{
    echo "$(date '+%Y-%m-%d %H:%M:%S')"
    mysql -u vicidialuser -p asterisk -N << 'EOF'
SELECT 
    TABLE_NAME,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) as size_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'asterisk'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;
EOF
    echo "---"
} >> "$MONITOR_LOG"

Add to crontab to run weekly:

0 3 * * 0 /usr/local/bin/monitor_db_size.sh

Section 7: Common Maintenance Scenarios

Scenario 1: Database Bloat (90% Disk Full)

Situation: Your 500GB drive is 90% full, ViciDial is slow, users complaining.

Action plan:

# 1. Check current size
du -sh /var/lib/mysql/asterisk/

# 2. Archive aggressively
/usr/share/astguiclient/ARCHIVE_MANAGER.pl \
  --archiveolderthandays 90 \
  --deletearchived 1

# 3. Clean orphaned records
mysql -u vicidialuser -p asterisk << 'EOF'
DELETE FROM vicidial_log WHERE call_date < DATE_SUB(NOW(), INTERVAL 120 DAY);
DELETE FROM vicidial_closer_log WHERE call_date < DATE_SUB(NOW(), INTERVAL 120 DAY);
OPTIMIZE TABLE vicidial_log;
OPTIMIZE TABLE vicidial_closer_log;
EOF

# 4. Verify space reclaimed
du -sh /var/lib/mysql/asterisk/

Expected result: 200-300GB freed, database responsive again.

Scenario 2: Query Timeouts

Situation: Agent screen takes 5+ seconds to load, database CPU at 100%.

Action:

# 1. Identify slow queries
mysql -u vicidialuser -p asterisk << 'EOF'
SELECT * FROM vicidial_log LIMIT 5;
EOF
# Time this—if > 2 seconds, table is too large

# 2. Archive older records
/usr/share/astguiclient/ARCHIVE_MANAGER.pl \
  --archiveolderthandays 180 \
  --deletearchived 1

# 3. Rebuild indexes
ALTER TABLE vicidial_log ENGINE=InnoDB;
ALTER TABLE vicidial_closer_log ENGINE=InnoDB;

# 4. Check again
SELECT * FROM vicidial_log LIMIT 5;
# Should be < 1 second now
EOF

Scenario 3: Unexpected Disk Full

Situation: Cron job failed, disk suddenly 100% full, MySQL won't start.

Emergency recovery:

# 1. Check what's consuming space
du -sh /var/lib/mysql/* | sort -rh | head -10

# 2. Manually archive highest-priority tables
# STOP ViciDial first to avoid conflicts
systemctl stop asterisk
systemctl stop php-fpm  # or apache2

# 3. Run archive with delete
/usr/share/astguiclient/ARCHIVE_MANAGER.pl \
  --archiveolderthandays 30 \
  --deletearchived 1

# 4. Restart services
systemctl start asterisk
systemctl start php-fpm

Section 8: Troubleshooting

ARCHIVE_MANAGER.pl Fails with "Permission Denied"

Symptom:

Error: Cannot access database (Permission denied)

Solution:

# 1. Check MySQL credentials
mysql -u vicidialuser -p asterisk -e "SELECT 1;"

# 2. Verify .my.cnf exists and is readable
ls -l /root/.my.cnf

# 3. Ensure file permissions
chmod 600 /root/.my.cnf

# 4. Test script directly
perl -e 'use DBI; print "Perl DBI installed\n";'

# 5. Run with explicit credentials
/usr/share/astguiclient/ARCHIVE_MANAGER.pl \
  --archiveolderthandays 180 \
  --deletearchived 1 \
  --user vicidialuser \
  --pass 'your_password'

Archive Tables Not Created

Symptom:

CREATE TABLE vicidial_log_archive... ERROR 1050: Table already exists

Solution:

# Check if archive tables exist
mysql -u vicidialuser -p asterisk -e \
  "SHOW TABLES LIKE '%archive%';"

# If not present, create manually
mysql -u vicidialuser -p asterisk << 'EOF'
CREATE TABLE vicidial_log_archive LIKE vicidial_log;
CREATE TABLE vicidial_closer_log_archive LIKE vicidial_closer_log;
EOF

MySQL Runs Out of Disk Space During Optimization

Symptom:

OPTIMIZE TABLE completed: Disk quota exceeded

Solution:

# OPTIMIZE TABLE requires free space equal to table size
# If table is 100GB, need 100GB free

# Option 1: Archive more records first
/usr/share/astguiclient/ARCHIVE_MANAGER.pl \
  --archiveolderthandays 60 \
  --deletearchived 1

# Option 2: Use OPTIMIZE with LOCK=NONE (MySQL 8.0+)
mysql -u vicidialuser -p asterisk << 'EOF'
ALTER TABLE vicidial_log ENGINE=InnoDB ALGORITHM=INPLACE, LOCK=NONE;
EOF

# Option 3: Extend MySQL partition
# This requires disk admin work, expand /var/lib/mysql mount
lvextend -L +200G /dev/mapper/vg-mysql
resize2fs /dev/mapper/vg-mysql

Cron Job Not Executing

Symptom: Maintenance log doesn't exist, files not archived.

Diagnosis:

# Check if cron is running
systemctl status cron
# or
systemctl status crond

# Check cron logs
tail -20 /var/log/syslog | grep CRON

# Verify crontab syntax
crontab -l
# Should show your job

# Test cron environment
env -i HOME=$HOME /bin/sh -c 'cd ~ && /usr/local/bin/vicidial-maintenance.sh'

# Check if cron user has permissions
sudo -u root crontab -l

Archive Inconsistency (Archive Count < Original Count)

Symptom:

vicidial_log: Supposed to copy 1,245,000 records
vicidial_log_archive: Only contains 1,200,000 records

Diagnosis:

# Check for foreign key constraint failures
mysql -u vicidialuser -p asterisk << 'EOF'
-- Look for records missing from related tables
SELECT COUNT(*) as orphaned_count FROM vicidial_log vl
WHERE NOT EXISTS (
  SELECT 1 FROM vicidial_campaigns vc WHERE vc.campaign_id = vl.campaign_id
);
EOF

# Solution: Run with --deletearchived 0 first (no delete)
/usr/share/astguiclient/ARCHIVE_MANAGER.pl \
  --archiveolderthandays 180 \
  --deletearchived 0

# Then investigate, fix orphaned records, try again

Section 9: Best Practices & Recommendations

Retention Policy Template

Define your retention policy upfront:

Document this in your runbook.

Backup Before Archival

Never skip backups:

# Before any major cleanup, backup
mysqldump -u vicidialuser -p asterisk > \
  /backup/pre-archive-$(date +%Y%m%d_%H%M%S).sql

# Verify backup integrity
mysql -u vicidialuser -p asterisk < \
  /backup/pre-archive-20240115_020000.sql 2>&1 | grep -i error

Monitor Active Calls During Maintenance

Never archive during peak hours:

# Check current active calls
asterisk -rx "core show channels"

# Example safe window output:
# Channel          Bridge              Exten   Pri   State (ms)     Since
# SIP/107-0000     (None)              s       1     Up    (5)       2024-01-15 14:23:14

Wait until < 10 active calls before starting maintenance.

Document Your Maintenance Schedule

Create /etc/vicidial/MAINTENANCE_SCHEDULE.md:

# ViciDial Maintenance Schedule

## Daily (2 AM)
- Archive records older than 180 days
- Delete archived API logs older than 30 days
- Optimize main tables
- Check database integrity

## Weekly (Sunday 3 AM)
- Full database backup
- Verify backup integrity
- Monitor database growth trend

## Monthly (1st of month, 4 AM)
- Archive records older than 90 days  
- Clean orphaned/test records
- Rebuild indexes
- Generate capacity report

## Quarterly
- Review retention policy
- Plan capacity expansion if needed

Summary

ViciDial's built-in admin utilities—primarily ARCHIVE_MANAGER.pl—provide a reliable path to production-grade database maintenance. Implementing systematic archival, cleanup, and optimization prevents the performance degradation and storage emergencies that plague unmaintained ViciDial deployments.

Key takeaways:

1. Archive regularly using ARCHIVE_MANAGER.pl with a 180-day threshold
2. Automate via cron to ensure consistent execution; don't rely on manual intervention
3. Monitor database size monthly and establish growth baselines
4. Test backups before every major archival operation
5. Schedule maintenance windows during off-hours when call volume is low
6. Optimize tables after large deletions to reclaim disk space
7. Document your policy so future admins understand your retention strategy
8. Plan for growth and extend storage before hitting 85% capacity

With disciplined maintenance, your ViciDial system will remain responsive, your backups manageable, and your infrastructure stable for years of reliable call center operations.