Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor to Delete data


Cursor to Delete data

Author
Message
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
Hey Guys,

i have to delete data from a table which is older than 2 weeks, how can i use a cursor to do it.

I will have to place the query in a SQL job and run that weekly once, please help me out.
Sean Lange
Sean Lange
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: 16616 Visits: 17024
Tej_7342 (11/5/2013)
Hey Guys,

i have to delete data from a table which is older than 2 weeks, how can i use a cursor to do it.

I will have to place the query in a SQL job and run that weekly once, please help me out.


Why do you think you need a cursor for a delete? Cursors are horrible for performance and there is absolutely no need for a cursor for this type of thing.

_______________________________________________________________

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 Moden's 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)
LinksUp
LinksUp
Right there with Babe
Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)

Group: General Forum Members
Points: 736 Visits: 4002
Tej_7342 (11/5/2013)

i have to delete data from a table which is older than 2 weeks, how can i use a cursor to do it.



Why does it have to be a cursor?

Could you do something like this:


delete from Table1 where Table1.DateTimeColumn < dateadd(ww, -2, getDate())



This would need to be tweaked to account for the time component. Other than that, this would run MUCH faster than any cursor.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
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
The log file fills up if i am using the query. (Database is in Simple Recovery). Any ideas?
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8556 Visits: 18143
Something like this might work, but you should test the correct numbers depending on your server.

  DECLARE @i int = 1  
WHILE @i > 0
BEGIN
DELETE TOP (1000000) MyTable
WHERE Table1.DateTimeColumn < dateadd(ww, -2, getDate())
SET @i = @@ROWCOUNT
BEGIN END



Or if you have a trigger on the table that might alter @@ROWCOUNT value.

WHILE EXISTS (
SELECT TOP 1 1
FROM MyTable
WHERE Table1.DateTimeColumn < dateadd(ww, -2, getDate())
)
BEGIN
DELETE TOP (1000000) MyTable
WHERE Table1.DateTimeColumn < dateadd(ww, -2, getDate())
SET @i = @@ROWCOUNT
BEGIN END




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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8556 Visits: 18143
Please don't send private messages, you limit the help you can get.
Tej_7342 (11/5/2013)

It looks good, but can this be modified to be generic instead of hard-coded. Also the problem is its fills up logs if we run it.

What do you mean by generic? What's wrong with the code? You said you needed to delete data from a table which is older than 2 weeks. As we can't see your data, we're just guessing.


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
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
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.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8556 Visits: 18143
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.

So, what's the problem with my code?


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
Sean Lange
Sean Lange
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: 16616 Visits: 17024
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.


Given that I might suggest a different approach entirely. Instead of trying to delete 2.18 billion rows out of 2.2 billion it is probably easier to insert the rows you want to keep into a new table. Then drop the current table and rename the new table.

Something like this:


select *
into MyCopy
from MyCurrentData
where SomeDate > DATEADD(week, -2, getdate())

drop table MyCurrentData

exec sp_rename 'MyCopy', 'MyCurrentData'



Make sure you script all the indexes/constraints prior to this because the select into will NOT bring over any indexes or constraints.

As with anything, make sure you try this on a test system first. :-P

_______________________________________________________________

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 Moden's 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)
DrKiller
DrKiller
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 241
Tej_7342 (11/5/2013)
The log file fills up if i am using the query. (Database is in Simple Recovery). Any ideas?


Why not consider running the job daily then instead of weekly? I'm assuming that the data are inserted on a daily basis.

Edit: Talking about this query


delete from Table1 where Table1.DateTimeColumn < dateadd(ww, -2, getDate())


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