Delete large data and effects on transaction log

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Delete large data and effects on transaction log

Author

Topic

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2013-12-03 : 02:11:08

Hi All,

I have to delete large amount of data which is archived in database already.
I wrote below query for deleting the records in table which has around 25 millions of records in batch with transaction.

DECLARE @delcount INT
SET @delcount = 1

WHILE @delcount > 0
BEGIN
BEGIN TRY

BEGIN TRANSACTION

;WITH CTE1 AS
(SELECT TOP 10000 ca.<ColumnName>
FROM <TableName> ca
WHERE <ColumnName> <= 26692272 )
DELETE ct
FROM CTE1 ct with (ROWLOCK)

SET @delcount = @@ROWCOUNT

COMMIT TRANSACTION;

END TRY

BEGIN CATCH
IF @@TRANCOUNT >0
ROLLBACK;
END CATCH
END

i wanted to know
1) what will be effect of it on transaction log
2) how to know what space is used by my deletion process in transaction log.
3) what happens in transaction log when each batch/transaction of 10000 records is completed

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-03 : 02:44:41

Whats your current recovery model?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2013-12-03 : 02:53:15

Full Recovery Mode

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-03 : 06:39:35

then it will log each and every record deletion info and within a batch it will commit transaction once it encounters the commit statement but transaction log will not be truncated until it gets backed up.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kristen
Test

22859 Posts

Posted - 2013-12-03 : 10:28:24

Is it crucial that the records are committed as a single batch?

We delete archive records in a loop, deleting, say, 100,000 rows per iteration and then "delay" for 2 seconds to give other processes time to run. We delete oldest first, and we don't care if the process deletes only some of the records - the remainder will be deleted tomorrow - so we don't use a transaction block.

We schedule Log Backups to run every 2 minutes during this time so that the delete does not fill the Log File excessively.

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2013-12-04 : 02:49:24

Thanks Visakh and Kristen for ur comments.

so can we increase the batch number from 10,000 to 100,000 and not keep transaction block ?

can we shrink the transaction log in between while query is executiong . i dont know much about it but i heard it from friends.

Kristen
Test

22859 Posts

Posted - 2013-12-04 : 03:45:01

If you run your database in Full Recovery Model then ALL transactions are kept until the next Log Backup; the Log Backup will release space from any committed transactions, and that space will be reused "Round-Robin"). This allows you to restore the last FULL backup (last night, say) and then all subsequent LOG backups (lets say that you take those every 10 minutes) so you will lose a maximum of 10 minutes work if you have a system failure. For a business critical database this is usually an essential requirement.

If you don't need this capability set your database to SIMPLE and then the log file will only extend to the size required for the SINGLE largest transaction (whereas with Full Recovery the log file size will be the total transactions is the busiest 10 minutes [i.e. the log backup interval])

If your code says:


WHILE @intRowCount > 0
BEGIN
   DELETE TOP 10000 FROM MyTable WHERE DeleteFlag = 1 -- This is not valid syntax! just for example
   SELECT @intRowCount = @@ROWCOUNT
END

then each batch of 10000 deletions will be committed at each loop iteration. If you run a Log Backup then the space used by those transactions will become free and made available for new transactions. i.e. your Log File will not fill up.

OTOH if you do:


BEGIN TRANSACTION
WHILE @intRowCount > 0
BEGIN

   DELETE TOP 10000 FROM MyTable WHERE DeleteFlag = 1 -- This is not valid syntax! just for example
   SELECT @intRowCount = @@ROWCOUNT
END
COMMIT

then the transactions will be added to the log file as they occur, but they will NOT be cleared by a backup UNTIL the COMMIT has finished. So now your Log File has to be big enough to hold the WHOLE job. (If that is what your business requirements are [i.e. "Either all batches are deleted, or none of them are"] then fine - you just need a bigger log file. But if it is not a business requirement [i.e. "If only some of the purged records are deleted, and the rest are done tomorrow, that's fine"] then you don't need the Transaction block.

Do not EVER (with one exception

) shrink the log file. Repeated shrinking just fragments the file and makes performance worse, over time. It is also bad practice.

Shrinking the log file will not do anything UNTIL the transactions have been backed up, and the space freed, so actually trying to use Shrink in this situation won't do anything. The correct solution is just to take more frequent log backups (you can do them more frequently during the Purge routine, which is what we do, or you can have them run every 10 minutes, 24/7, and leave it like that, and let the Log file grow to the size it needs to be for the worst-case transaction load in a 10 minute period).

The exception on Log Shrinking is if you have a one-off unusual event that increases the log file size unexpectedly. In that case shrink it back to its "Normal working size". If you have to shrink it [because it has grown bigger than its "normal working size"] more than twice a year, say, then someone needs to look at what SQL code is causing the Log File to be extended, and then take steps to fix/improve the situation. Do not use Shrink File as a band-aid

Kristen
Test

22859 Posts

Posted - 2013-12-04 : 04:10:24

Note that if you take a Log Backup only once a day (which would be very bad practice!) and it is, say, 144MB then if you took a Log Backup every 10 minutes each file would be, on average, 1MB (plus a little bit extra for the overhead of the file), so you would have 144 separate files, 24 hours x 6 backups per hour, and the total filesize of the backups would still be 144MB (plus the extra small overhead), so there is no reason not to make log backups every, say, 10 minutes.

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2013-12-04 : 08:23:13

Thanks Kristen for the explaination

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources