Re: [Emerald] Consolidation

Jeff Woods ( )
Fri, 04 Feb 2000 10:26:11 -0500

At 08:20 AM 2/4/00 -0600, you wrote:

>Calls consolidation worked fine last week but this week I am getting this
>error, please help..
>SQL Server Error: 229 EXECUTE permission denied on object sp_tempdbspace,
>database master, owner dbo
>State=1, Severity=14

Looks like your TEMPDB is full in your master database.

You'll want to both clear and truncate it after you have space in
it. Increase the size of your TEMPDB in the master database (not in the
Emerald one). You may need to increase the size of the Database DEVICE
before you can increase the size of the database.

How long does consolidation take? If longer than a minute or two, then you
will want to clobber off some old calls in the calls table. Do you
regularly delete entries older than a very few months from your calls
table? (Having a huge calls table is the single biggest slowdown factor
in both searches in Emerald, and in Consolidation -- if the entire calls
table cannot be cached in the RAM made available to SQL server....which is
only 32 MB IIRC.... your searches drag, where if the whole thing fits in
RAM Cache, a search or consolidation smokes. Ensure that as much RAM as
you have, less a smidge, is allocated to MSSQL (see archives for this, or
ask), and that the calls table is regularly cleared of calls older than 3
months, after making backups. Also regularly kill off all AcctStatusType =
1 (START) records that are OLDER than your oldest current call. If your
longest call currently listed online goes back to January 24th (dedicated
line), you can kill off all such records from January 23rd and prior, since
all the data needed for accounting is in the type 2 (STOP) records. If
you've never done this, do this in chunks to avoid filling the TempDB as
well (perhaps two weeks at a time). See archives, or ask how to do this.

Folks still using MSSQL 6.5 will see an AMAZING performance increase if
they do these things:

1) Allocate all but about 32 MB of RAM to SQL. The more the better.

2) Keep the Calls Table as small as possible.
A) Consolidate
B) Delete Type 1 records regularly for all completed calls
C) Delete Type 2 records regularly for all CONSOLIDATED calls
(I recommend keeping as much as four months of type 2's
simply because you will have some quarterly paying accounts).

3) Defrag your hard drive, with SQL shut down entirely, at least once
each three months. SQL is rather hard on fragmentation, and THAT can
really slow down SQL, especially on searches, when the hard drive
has to constantly move around to find all of a file.

For more information about this list (including removal) go to: