Re: [Emerald] Database Size

Josh Hillman ( (no email) )
Mon, 22 Mar 1999 13:33:46 -0500

From: Alexander Blauvelt <>
>Our emerald database has grown to an incredible size, and 90% of it is
>in the calls table. It now has over a million calls logged in the
>table. I try to use the emerald administrator to 'Trim' all the calls,
>but whenever I do this, the database only grows more and I have to
>expand the size of it and then the calls never get cleaned off. How can
>I get rid of these calls in the calls table? Can I simply drop the
>table and then run the Update Database script to recreate it?

Open up ISQL and run the following:
select min(calldate) from calls

Make note of the date.

Then little by little, start wiping out old data from the Calls table. I
generally wipe out the oldest 2 weeks, once every two weeks, but retaining
close to 3 months worth of sessions (~300,000 sessions). One key thing is
that I have a script run every night that wipes out all "null" username
records and all Start records that are older than 3 days. This is a nice
automated way of keeping the table trimmed. I do not wipe out the 2-weeks
worth of (any) sessions automatically--I'd rather do that manually.

delete from calls
where calldate < '12/1/98'

Change that date to somewhere around 2 weeks after the date returned in
first query above.

After the above script finishes (may take quite a while), rerun it,
incrementing the date again by another two weeks (or less, if you
encountered a problem the first time).

In MSSQL Enterprise Manager, I created a scheduled task (type=TSQL) that
runs the following "command" every night at 4:30am:

WHERE (AcctStatusType = 1 OR UserName = 'null')
AND DateDiff(Day, CallDate, GetDate()) > 3

We don't have a reason to use Calls Consolidation, so I only keep this stuff
around for statistical info, troubleshooting customer problems, etc.


>And here's stupid question #34 from me, but how do I do a
>'consolidation' that everyone talks about? I assumed we wouldn't need
>this because we don't do any type of time banking here. Where is the
>command to do it ? The emerald admin has Update Calls, Update Database
>and Check Database as the options to run in the database maintainence
>Online Gateway
>For more information about this list, including removal,
>please see

For more information about this list, including removal,
please see