/* Script to DELETE records older than 6 months from the tables call and waste !!! WARNING: BEFORE YOU RUN THE SCRIPT SURE THAT YOU HAVE DONE THE BACKUP DATABASE !! After running the script run the shrink database. */ declare @datarif as datetime declare @numdel as int declare @numero_mesi int declare @numero_record_da_eliminare int -- To change the number of months to keep changing the number 6 below in instructions with the number of months wanted -- If today is 25/12/2014 the script will go back six months from 1/12/2014, and then return 01-06-2014 -- And then deletes all previous calls on 01-06-2014 set @numero_mesi = -6 if @numero_mesi>=0 begin RAISERROR (N'Attention! the number of months MUST be less than zero otherwise will be deleted ALL RECORDS!', -- Message text. 18, -- Severity, 1 -- State, ); -- Second argument. end set @numero_record_da_eliminare = 10000 print 'Will be removed internal calls prior to:' print dateadd(m,@numero_mesi,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) select @datarif = dateadd(m,@numero_mesi,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) select @numdel = count(*) from chiamateinterne where ddata < @datarif print 'Number of internal calls to be deleted: ' + cast(@numdel as varchar(50)) select @numdel = count(*) from scarti where ddata < @datarif print 'Number of rejects calls to be eliminated:' + cast(@numdel as varchar(50)) /*******************************************************************************************/ -- SECTION RUNNING THE CANCELLATION OF RECORD -- On a Pentium Core 2 Duo 2:33 GHz are eliminated approximately 100,000 rows every 4 minutes. -- If the script is interrupted deleted records OTHERWISE ARE NOT RESTORED. -- You can then run the script and break it to see the number of records remaining. /********************************************************************************************/ -- Note: Remove the comments '/ *' and '* /' from the code below to delete the records /* declare @num int print 'Deleting record of internal calls' Set @num = (Select Count(*) from chiamateinterne where ddata < @datarif) set rowcount @numero_record_da_eliminare --elimino N righe per volta per limitare l'impatto sul file di log While @num <> 0 Begin begin transaction Delete from chiamateinterne WITH (NOWAIT) where ddata < @datarif commit transaction Set @num = (Select Count(*) from chiamateinterne where ddata < @datarif) end set rowcount 0 */