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?
use
SET NOCOUNT ON
these messages do not appear and saves on network traffic.– MauroAlmeida
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.
– anonimo