September 18, 2008 at 6:00 am
Hi All,
Currently we have one SQL job on Production and it is going to delete the records which are older than 7days from the ABC table.But the no of records in ABC table are 1crore and while executing this job,it is deleting records more than 5,00,000. But my problem is when ever this job gets executed log file becomes full(As delete is making an entry into log).Could you please suggest is there any way to overcome this problem.
Regards,
MG
September 18, 2008 at 6:38 am
Add disk space.
And normalize data in order to decrease row size in long table.
If row size would not exceed 40 bytes deleted amount of data would be just 200M - nothing really.
Of course there must be CLUSTERED index on datetime column. Otherwise number of pages affected by delete significantly grows.
_____________
Code for TallyGenerator
September 18, 2008 at 6:40 am
If your recovery mode is full/bulk logged the
Before deleting record set database to simple mode
And after deletion is over
set database to full/bulk logged.
September 18, 2008 at 6:53 am
There is no really point to turn back to full mode.
Log file sequence is already broken, no much use of it.
_____________
Code for TallyGenerator
September 18, 2008 at 6:53 am
That will help if the delete is batched and a checkpoint is run between batches. If the deletes are happening all in one transaction, then the log will still run out of space, even on simple recovery.
MG: Can you batch the deletes? Delete 500,000 rows at a time with either a checkpoint (if in simple recovery) or a transaction log backup (if in full/bulk logged recovery) between the batches.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 18, 2008 at 6:54 am
vyas (9/18/2008)
If your recovery mode is full/bulk logged theBefore deleting record set database to simple mode
And after deletion is over
set database to full/bulk logged.
And do a full database backup since the log chain is broken
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 18, 2008 at 7:41 am
Gail Shaw-As it in Production, database is in Full recovery model, we can change it to Simpel while deleting. As you said Second option how can I delete 500,000 rows at a time with transaction log backup between batches.Could you please exlainb it in more?
Thanks,
MG
September 18, 2008 at 8:12 am
Here's the gist of the logic, it's in pseudo-code because I don't have a server to test it on ATM.
Make sure you test on dev server first!
SET ROWCOUNT 500000
DELETE FROM TABLE WHERE WHATEVER
WHILE @@RowCount = 500000
begin
backup log...
DELETE FROM TABLE WHERE WHATEVER
end
backup log...
SET ROWCOUNT 0
September 18, 2008 at 9:39 am
maheshgilla (9/18/2008)
Gail Shaw-As it in Production, database is in Full recovery model, we can change it to Simpel while deleting. As you said Second option how can I delete 500,000 rows at a time with transaction log backup between batches.Could you please exlainb it in more?Thanks,
MG
I'd suggest you don't switch to simple, rather keep the DB in full recovery and do your deletes the way Ninja's listed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 18, 2008 at 9:54 am
Also make sure you have enough HD space whereever you put the backups!!!
September 18, 2008 at 10:03 am
What would be the advantage with taking transaction log backup between delete batches and how does it going to reduce the log size.We have 15 Min Transaction Log Backup job also on our platform.
September 18, 2008 at 10:13 am
The advantage is that the log backup will clear out of the log the entries for the delete that just finished, allowing the space to be reused on the next delete. Hence you shouldn't run out of space.
How long does the delete of the 5 million rows take?
You probably want to do a checkpoint right before the log backup, to ensure that the log records for the delete are inactive
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2014 at 2:57 pm
Try this link
http://sqlism.blogspot.com/2014/09/script-to-delete-millions-of-records.html
Delete records without increasing your log space
------------------------------------------------------------------
DECLARE @continue INT
DECLARE @rowcount INT
SET @continue = 1
WHILE @continue = 1
BEGIN
PRINT GETDATE()
SET ROWCOUNT 10000 --Replace 10000 as required
BEGIN TRANSACTION
DELETE
FROM dbo.Transactions
WHERE TranDate IS NULL --Replace your delete script here
SET @rowcount = @@rowcount
COMMIT
PRINT GETDATE()
IF @rowcount = 0
BEGIN
SET @continue = 0
END
END
October 7, 2014 at 3:15 pm
SQListic (10/7/2014)
Try this linkhttp://sqlism.blogspot.com/2014/09/script-to-delete-millions-of-records.html
Delete records without increasing your log space
------------------------------------------------------------------
DECLARE @continue INT
DECLARE @rowcount INT
SET @continue = 1
WHILE @continue = 1
BEGIN
PRINT GETDATE()
SET ROWCOUNT 10000 --Replace 10000 as required
BEGIN TRANSACTION
DELETE
FROM dbo.Transactions
WHERE TranDate IS NULL --Replace your delete script here
SET @rowcount = @@rowcount
COMMIT
PRINT GETDATE()
IF @rowcount = 0
BEGIN
SET @continue = 0
END
END
Not only is this thread 6 years old you have a major flaw in your code. The flaw is using ROWCOUNT for deletes. This has been deprecated and will stop working in the future. http://msdn.microsoft.com/en-us/library/ms188774.aspx
The approach that was suggested long ago using a loop and deleting the top xxx rows is better. It doesn't use deprecated features and it is much easier to decipher.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 7, 2014 at 3:17 pm
Thanks for correcting me Sean
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply