SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Delete large data and effects on transaction log
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vishal_sql
Posting Yak Master

101 Posts

Posted - 12/03/2013 :  02:11:08  Show Profile  Reply with Quote
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.

Edited by - Vishal_sql on 12/04/2013 01:47:37

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/03/2013 :  02:44:41  Show Profile  Reply with Quote
Whats your current recovery model?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Vishal_sql
Posting Yak Master

101 Posts

Posted - 12/03/2013 :  02:53:15  Show Profile  Reply with Quote
Full Recovery Mode
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/03/2013 :  06:39:35  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/03/2013 :  10:28:24  Show Profile  Reply with Quote
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.
Go to Top of Page

Vishal_sql
Posting Yak Master

101 Posts

Posted - 12/04/2013 :  02:49:24  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/04/2013 :  03:45:01  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/04/2013 :  04:10:24  Show Profile  Reply with Quote
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.
Go to Top of Page

Vishal_sql
Posting Yak Master

101 Posts

Posted - 12/04/2013 :  08:23:13  Show Profile  Reply with Quote
Thanks Kristen for the explaination
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000