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
Your Name Here
Your Name Here
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 783
Re: I will have to place the query in a SQL job and run that weekly once, please help me out.

With 2.2 billion records in your table, why would you think running a delete job [once a week] - using a cursor of all things - was a good idea? I'm not trying to rip into you but as a DBA for what sounds like a very active production environment you have to make some basic decisions on the management of your data based on the business need to retain two weeks of data. I'm glad you posted your question in the forum to ask for help but you also need to consider the possibility that your constraint to run this purge weekly may have been a bit misguided.

Several of the replies took that "weekly" constraint as a fixed situation and provided some solid solutions (nod to the respondents) but the simple fact that you want to run this massive purge once a week suggests to me that you really need to evaluate this situation closely before asking for help.

I suggest reviewing the reason for retaining two weeks of data. Is it because Marketing wants a rolling two weeks? Accounting? If so, identify the granularity of the data they need. Do they run a report once a day or every hour? Do they need summary or detail data to report on? Maybe you can "pre-run" that summarization with results going to a reporting table.

In my humble opinion, if given this situation as my own, I would run this purge hourly - possibly right after "the report" was completed. The size of the transaction log would "self-level" to the size it needs to be to handle the delete and would only grow to fit the most active hour during the day.

If that's not an option, I suggest you invest in more disk space and extend your transaction log file to allow you to successfully complete your delete. In this case I'd choose one of the prior suggestions that limit the number of records processed or run the delete based on the record date/time with intervals that limit the number of records in each "batch".
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
New data will inserted every day so, we would want to keep only 2 weeks worth of data. Hence we would want to implement something recursive which will delete on daily or weekly basis. (Without hurting the logs)
webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3025 Visits: 3745
Tej_7342 (11/7/2013)
New data will inserted every day so, we would want to keep only 2 weeks worth of data. Hence we would want to implement something recursive which will delete on daily or weekly basis. (Without hurting the logs)


I'd recommend listening to what Luis and Sean recommended, or some combination. Also, "Your Name Here" make great points in suggesting ways to evaluate the "weekly" constraint and the granularity of the data.

Finally, some combination of the above, along with possibly using a backup to do a lot of the purging off of the production system and then adding the difference (whatever was added since the backup) might alleviate stress on the production log files.

It also sounds to me like Full Recovery with log backups should be planned for, but the experts here know better than I do whether that is feasible for a db with 2 billion rows (might make sense after the purge).

Good luck,
webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
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
Thanks a lot guys, it worked perfectly.

Only one question after the deleting the reserved space did not go down, it was the same as before any reason for that?
salilkol
salilkol
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 47
what solution u implemented ?
Your Name Here
Your Name Here
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 783
@Silok

Re: "Only one question after the deleting the reserved space did not go down, it was the same as before any reason for that?"

I'd strongly suggest reading the manual. If you're responsible for a production SQL Server, you absolutely need to know the basics. Challenge yourself to at least research your question before you post. Querying the forum for the simple SQL Server questions you have could very well be a point of discussion with your boss at your termination.

http://msdn.microsoft.com/en-us/library/ms189493.aspx

BTW: I'd encourage you to start with the database backup and recovery topics.
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
I'm With Sean!

To get the table into a managable state, copy the records you want to keep then truncate the real table and then either drop it and rename the original or copy the records back into it.

going forward you need a decent DBA: I would suggest partitioning the table along period lines and they you can swich out or delete partitions as required.
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2171 Visits: 12474
Grant Fritchey has posted at least a few database maintenance videos on YouTube. Definitely worth watching if you're not really comfortable with backup/restore. Absolutely something to practice at home until you can do it in your sleep. Here's a link so you can watch all you want...

http://www.youtube.com/user/GrantBrFr55
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