DELETE Calls
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 michael@abraxis.com=>>===================================================>> > ==================================================== /\ Mike A. Miller == /--\ \/ Abraxis Networks ==/ \ B R A /\ I S == / == N E T W O R K S michael@abraxis.com====================================================