I have a database where there are like billions of rows and I need to delete the data based on some condition.
Initially I was trying to delete the code in one transaction and log starts growing to the hard disk and fails, then I tried to do it in chunks.
SET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;
WHILE @r > 0
BEGIN
BEGIN TRANSACTION;
DELETE TOP (2000000) -- this will change
Databasename.dbo.Tablename
WHERE loading_time < '08/27/2014 4:00:00'
SET @r = @@ROWCOUNT;
COMMIT TRANSACTION;
-- printing if transaction is commited
print 'done'
-- shrinking file after transaction is comminted
DBCC SHRINKFILE (2, TRUNCATEONLY); -- CHECKPOINT; -- if simple
-- BACKUP LOG ... -- if full
END
Is this the right way to delete data and delete logs, in between user transaction because when I did it I get an error ;
Cannot perform a shrinkfile operation inside a user transaction. Terminate the transaction and reissue the statement.
Aucun commentaire:
Enregistrer un commentaire