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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13180 Visits: 4077
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
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 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
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41710 Visits: 19815
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
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62201 Visits: 17954
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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13180 Visits: 4077
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
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 547
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