Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Cursor to Delete data Expand / Collapse
Author
Message
Posted Tuesday, November 05, 2013 11:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:00 PM
Points: 7, Visits: 58
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.
Post #1511574
Posted Tuesday, November 05, 2013 11:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 12,031, Visits: 11,060
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)
Post #1511576
Posted Tuesday, November 05, 2013 11:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 319, Visits: 1,148
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/
Post #1511578
Posted Tuesday, November 05, 2013 12:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:00 PM
Points: 7, Visits: 58
The log file fills up if i am using the query. (Database is in Simple Recovery). Any ideas?
Post #1511591
Posted Tuesday, November 05, 2013 12:46 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 2,784, Visits: 5,965
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1511596
Posted Tuesday, November 05, 2013 1:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 2,784, Visits: 5,965
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1511605
Posted Tuesday, November 05, 2013 1:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:00 PM
Points: 7, Visits: 58
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.
Post #1511610
Posted Tuesday, November 05, 2013 1:37 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 2,784, Visits: 5,965
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1511615
Posted Tuesday, November 05, 2013 1:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 12,031, Visits: 11,060
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.


_______________________________________________________________

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)
Post #1511616
Posted Wednesday, November 06, 2013 12:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:47 AM
Points: 176, Visits: 145
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())

Post #1511733
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse