SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor to Delete data


Cursor to Delete data

Author
Message
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5300 Visits: 4076
Tej_7342 (11/5/2013)
There are 2.2 billion records in the table and we want to only keep 2 weeks data, as of now we are lagging behind so we want to delete data in smaller chunks first so that the logs don't get messed up.
you should look for batch approach for these type of deletions

see http://www.sqlservercentral.com/articles/Top/63301/

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
DrKiller
DrKiller
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 245
Bhuvnesh (11/6/2013)
Tej_7342 (11/5/2013)
There are 2.2 billion records in the table and we want to only keep 2 weeks data, as of now we are lagging behind so we want to delete data in smaller chunks first so that the logs don't get messed up.
you should look for batch approach for these type of deletions

see http://www.sqlservercentral.com/articles/Top/63301/


And indexing ;-)
steven.ensslen
steven.ensslen
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 33
If I had to guess, the problem is that a single DELETE is too large to fit in the transaction log. DELETE is the wrong tool for the job. A cursor loop reduces the size of each transaction, so they would not overflow the log, but it is still naive.

The correct solution here is to partition the table. The SWITCH out the old partition with all of its data.
http://technet.microsoft.com/en-us/magazine/2007.03.partitioning.aspx
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16687 Visits: 19108
steven.ensslen (11/6/2013)
If I had to guess, the problem is that a single DELETE is too large to fit in the transaction log. DELETE is the wrong tool for the job. A cursor loop reduces the size of each transaction, so they would not overflow the log, but it is still naive.

The correct solution here is to partition the table. The SWITCH out the old partition with all of its data.
http://technet.microsoft.com/en-us/magazine/2007.03.partitioning.aspx


It depends as the OP is not interested on keeping old data.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
steven.ensslen
steven.ensslen
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 33
Luis Cazares (11/6/2013)
steven.ensslen (11/6/2013)
If I had to guess, the problem is that a single DELETE is too large to fit in the transaction log. DELETE is the wrong tool for the job. A cursor loop reduces the size of each transaction, so they would not overflow the log, but it is still naive.

The correct solution here is to partition the table. The SWITCH out the old partition with all of its data.
http://technet.microsoft.com/en-us/magazine/2007.03.partitioning.aspx


It depends as the OP is not interested on keeping old data.


I disagree. If we want to get rid of the data SWITCH the partition then to DROP or TRUNCATE the destination table will be thousands of times faster/less-work-for-the-db than a DELETE. The only reason for the DELETE would be some sort of transaction replication, like log-shipping for Disaster Recovery.
Silok
Silok
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 65
Yes a delete would fill up the logs.
Right now the older data is deleted but going further we would to put in a practice which will delete data which gets older than 2 weeks.

May be put in a cursor and delete one day at a time. Any other way to accomplish this?

Thanks in Advance
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26305 Visits: 17553
By far the simplest approach here is to use the technique I described. I guess since everyone is completely ignoring it that either I have lost my mind or nobody even read it.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
steven.ensslen
steven.ensslen
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 33
Sean Lange (11/6/2013)
By far the simplest approach here is to use the technique I described. I guess since everyone is completely ignoring it that either I have lost my mind or nobody even read it.


Sean, I like your solution. It is simple and it will usually work. But it won't scale to handle this problem (billions of rows) on their hardware. They need a more complex solution that does less IO and doesn't put pressure on their transaction log.
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5300 Visits: 4076
steven.ensslen (11/6/2013)
They need a more complex solution that does less IO and doesn't put pressure on their transaction log.
IF you handle the records in bunches .. it will take care of IO plus transaction log management. thats the reason i recommended you "Batch approach deletion".

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
B's-Data
B's-Data
SSC Veteran
SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)

Group: General Forum Members
Points: 298 Visits: 534
I would suggest you export the records that you want into another table and then drop the original table. If you need the table named back to the original, that is easy to do.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search