Deleting records from a very large table

Asked

Viewed 28 times

0

We have a table on Sqlserver with over 154 million records and we need delete all records whose DTANO is less than 2017.

But the bank is accessed via WEB and, of course, can not get stuck.

Trying to resolve the issue I wrote the following script:

WHILE ( 0 = 0 )
 BEGIN
   IF NOT EXISTS( SELECT TOP (1) * FROM TBL712 WHERE DTANO < 2017  )
     BREAK

    IF ( DATEPART( HOUR, GETDATE()) = 6 ) 
    BEGIN
      IF (( DATEPART( weekday, GETDATE()) > 1 ) AND ( DATEPART( weekday, GETDATE()) < 7 ))
      BEGIN
        WAITFOR DELAY '13:00:00'
      END
    END

   ;WITH CTE AS ( SELECT TOP (1000) * FROM TBL712 WHERE DTANO < 2017 )
     DELETE CTE

 END

At 06:00 the script runs WAITFOR 13 hours. Therefore, it runs only at 19:00 at night, but even so, the access to the system is very slow when the script is running.

I thought at this point, with the script suspended, the interference would be minimal, not to mention none.

When the script is canceled and sends all those messages:

(1000 Row(s) affected)

(1000 Row(s) affected)

(1000 Row(s) affected)

(1000 Row(s) affected)

It returns to function reasonably.

There’s how I send these messages while the script still runs?

What I could improve, or how to make it different?

  • 1

    use SET NOCOUNT ON these messages do not appear and saves on network traffic.

  • Have you thought about changing your strategy? Copy all the records that must remain to an auxiliary table and after the copy is finished, drop the old table and rename the auxiliary table. I believe the downtime will be much smaller.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.