Re: SQL consolidation and user logins

Mike Miller ( )
Mon, 10 Aug 1998 15:19:40 -0400

The exact query (very simple):

WHERE CallDate < DATEADD(day, -60, getdate())

Weekly and monthly consolidation takes much longer (as in *hours* to
complete)... I know because our initial consolidation long ago *really*
hurt us!

Is there a better query I should use?

--At 02:46 PM 8/10/98 -0400, you wrote:>It sounds like the query is hurting you.  Are you using a datediff to clear>it out (delete from calls where datediff(mm, calldate, getdate()) > 2)?  If>you are this seems to be taking too long each night.  I would suggest a>weekly our monthly removal (I'm using weekly now, and it only takes about>10 minutes each 7-10 days). >>Brian Lube>MPInet>>At 02:14 PM 8/10/98 -0400, you wrote:>>We are using RadiusNT 2.2 with ODBC and a MS-SQL 6.5 sp4 SQL database.  We>>keep 2 months accounting for our records.  Our database is generally has>>around 300MB to 400MB of space used to handle this.  Every morning at 4AM>>SQL executive runs a script that deletes all calls records over 2 months>>old.  According to SQL executive, this process has been taking between 12>>and 15 minutes to complete each night.  Our problem is that during the>>consolidation process, there are periods of time in which no users can log>>onto the network.  The Radius screen itself just freezes on an accounting>>request until consolidation completes.  Originally Radius was installed on>>the same machine as SQL Server, but because of the login problems we moved>>it onto a different machine from the SQL server... no luck, still freezes>>during consolidation.  The same copy of Radius is performing authentication>>and accounting.  I suspect that the Calls table is getting locked during>>the mass delete query, but am not sure if this is causing our problem or>>not.  Has anyone dealt with this?  Is there a workaround that will allow us>>to perform consolidation without locking out logins?  Would running two>>copies of radius on two seperate machines (one for authentication and one>>for accounting) help or am I heading the wrong direction with this?  >>>>-->>>>>>>>===================================================>>=  /\                          Mike A. Miller     =>>= /--\         \/              Abraxis Networks   =>>=/    \ B R A  /\ I S                             =>>=             /                                   =>>=      N E T W O R K S>>===================================================>> > ====================================================  /\                          Mike A. Miller     == /--\         \/              Abraxis Networks   ==/    \ B R A  /\ I S                             ==             /                                   ==      N E T W O R K S