dimanche 28 décembre 2014

Shrink logs in between user transaction in SQL Server


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