2) Have you run call consolidation, and/or cleaned up your calls table
since you started running Emerald?
Every time a user logs on, and every time they log off, RADIUSNT writes a
new element to the table called 'Calls'. As soon as they have logged off,
the record for the logon is redundant, and can be deleted. This is NOT
automatic!
Here's what you may want to do in the SQL Query window (the one with a
couple of boxes and a question mark, in SQL Enterprise Manager -- you
cannot do this from Emerald so you have to do it from SQL itself):
First find out how old your OLDEST start record is. This query will likely
take a while (potentially many many minutes, depending on the speed of your
SQL server, and especially if you haven't tweaked the maximum memory usage
for SQL server (and especially with 130 MB in use in your DB) but wait it out:
SELECT * FROM CALLS WHERE ACCTSTATUSTYPE = 1
Order By CallDate
Now, look at the results, locating the CALLDATE column of the table. Find
the oldest date. For this example, we'll assume it was 3/1/98. You'll
want to run several successive queries in that window, like so:
DELETE FROM CALLS
WHERE CALLDATE <= '3/31/98' AND ACCTSTATUSTYPE = 1
After this successfully completes (and the results window will show next to
no output, just something like: "12527 rows affected"), go truncate the
log again.
The reason we're doing this in small, monthly chunks like this is that this
table deletion of the useless start records is fairly demanding on the
transaction log, but once the deletion is complete, you don't need the
entries in the transaction log any longer.
Rinse, and repeat for each month:
DELETE FROM CALLS
WHERE CALLDATE <= '4/30/98' AND ACCTSTATUSTYPE = 1
(Truncate Transaction Log)
DELETE FROM CALLS
WHERE CALLDATE <= '5/31/98' AND ACCTSTATUSTYPE = 1
(Truncate Transaction Log)
Continue this through all but the last couple of weeks. Don't delete the
start record for ANY call that has not hung up yet. Thus, if you have a
dedicated customer, and he's been logged on since August 28th (as I have),
don't delete any start records (those of acctstatustype = 1) after that date.
When done, THEN go run your call consolidation, if you can and its properly
set up:
Batch
Click "Pre-Invoice Summary" (then wait -- this may take a while)
Click "Charges"
If your Calls table goes back very far, come back the next day -- the first
consolidation can take hours and hours, but after that, will be very short.
Consolidation takes all those tens of thousands of records left in the
calls table, the ones that say who, and how long, and how much downloaded,
etc, into single "one month" entries in another table.
There's no reason to keep even the STOP records in the calls table more
than a few months. Consolidate them, and then kill ALL records in the
Calls table older than, say, 6 months (or whatever is comfy for you):
DELETE FROM CALLS WHERE CALLDATE <= '4/1/99'
Then truncate the transaction log again, and make a new backup (diskdump),
and you'll probably find much mroe than 100 MB available in your database
again.
Do the cleanup of start records, and deletion of OLD stop records, every
month or so, and you'll keep a happy database.
Also note that doing all of the above regularly will VASTLY speed up the
queries of Emerald itself. How long does doing this on you system take?
Open an MBR
Click on Services Tab
Highlight a Login Service
Click on EDIT
Click on "Time Online" tab
Put in a Date range of 9/1/99 to 9/15/99
Click Update
On my system, that takes less than a second, using SQL 6.5. Admittedly,
the FIRST time I run such a query after restarting the SQL server, it can
take up to 15 seconds, but that's because it's reading from disk the first
time, and from RAM thereafter. If you can keep your entire Emerald DB
small enough to fit in the RAM you've let SQL have (and by default that's
only 16 MB -- ask some other time or STFA for details on how to give SQL
more RAM), then this sort of query, or even a "Select * From Calls", should
be almost instantaneous.
The more calls you get, the more diligent you should be about keeping your
Calls table clean.
At 11:26 AM 9/30/99 -0400, you wrote:
>I truncated the log as you said.
>My log size is 150MB and available was 1.5MB and is now 20MB
>
>I was able to run the scripts and clear my online table.
>
>Thanks for the help.
>Now I need to find out what is using 130MB of my 150MB log file.
>
>Any suggestions?
>
>Thanks,
>John
>
>
>*********** REPLY SEPARATOR ***********
>
>On 9/30/99, at 11:17 AM, Josh Hillman wrote:
>
> >From: PowerNet <psc5@powersupply.net>
> >When I click on truncate I get the message to immediately do a database
> >dump.
> >Is that what we are doing in this second procedure you sent?
> >
> >
> >You can do the diskdump afterward if you want. It can't hurt--it's just a
> >backup. The diskdump that it wants you to do isn't really directly related
> >to anything else I mentioned. SQL just wants you to do it as a saftey
> >measure. I have nightly scheduled tasks that run my diskdumps (unrelated to
> >the one you mentioned).
> >
> >Josh
> >
> >
> >*********** REPLY SEPARATOR ***********
> >
> >On 9/30/99, at 10:54 AM, Josh Hillman wrote:
> >
> >>> From: PowerNet <psc5@powersupply.net>
> >>> Msg 1105, Level 17, State 2
> >>> Can't allocate space for object 'Syslogs' in database 'Emerald' because
> >>the
> >>> 'logsegment' segment is full. If you ran out of space in Syslogs, dump
> >the
> >>> transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to
> >>> increase the size of the segment.
> >>> Msg 1105, Level 17, State 2
> >>> Can't allocate space for object 'Syslogs' in database 'Emerald' because
> >>the
> >>> 'logsegment' segment is full. If you ran out of space in Syslogs, dump
> >the
> >>> transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to
> >>> increase the size of the segment.
> >>>
> >>From: Josh Hillman <admin-maillist@talstar.com>
> >>> Go into SQL enterprise manager
> >>> find the Emerald database and double-click on it.
> >>> Click on Truncate Log.
> >>>
> >>> I'll send other suggestions for what to do after this in a few minutes.
> >>
> >>Here's part 2, from a message I sent to the list sometime last year:
> >>
> >>Go into SQL Enterprise Manager
> >>Click on the "+" next to your SQL server name.
> >>Right-click on Database Devices and choose New Device.
> >> Name: EmeraldLogs (or whatever you want)
> >> Size: ?? (mine is 35MB but 99% of the time, only about 1MB is
> >> being used because I have the Transaction Log
> >> "truncate on checkpoint" each night at 3:15am when the
> >> database is backed up (diskdump))
> >> Click on Create Now
> >>Click on the "+" next to Databases
> >>Double-click on Emerald
> >> Click on Expand
> >> Data Device: (none)
> >> Log Device: EmeraldLogs (or whatever you named it above)
> >> Size: (same as the value you selected above)
> >> Click on Expand Now
> >> Click on the Options tab.
> >> Check "Truncate Log on Checkpoint" to keep the logs small.
> >> Note: by doing this, you won't be able to backup the
> >> transaction logs when doing a diskdump (backup).
> >> Click Ok.
> >>
> >>I don't remember if you have to stop/restart MSSQL service or not.
> >>
> >>This will prevent your transaction logs from causing problems with running
> >>out of space for your data.
> >>
> >>Josh
> >>
> >>
> >>For more information about this list (including removal) go to:
> >>http://www.iea-software.com/support/maillists/liststart
> >
> >For more information about this list (including removal) go to:
> >http://www.iea-software.com/support/maillists/liststart
> >
> >
> >
> >For more information about this list (including removal) go to:
> >http://www.iea-software.com/support/maillists/liststart
>
>For more information about this list (including removal) go to:
>http://www.iea-software.com/support/maillists/liststart
For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart