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 Thursday, November 7, 2013 7:50 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 12:19 PM
Points: 139, Visits: 624
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".
Post #1512279
Posted Thursday, November 7, 2013 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 8:59 PM
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)
Post #1512297
Posted Thursday, November 7, 2013 8:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:35 AM
Points: 2,379, Visits: 2,742
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


-------------------
"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
Post #1512304
Posted Friday, November 8, 2013 7:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 8:59 PM
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?
Post #1512676
Posted Monday, November 11, 2013 5:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:07 AM
Points: 20, Visits: 45
what solution u implemented ?
Post #1513087
Posted Thursday, November 14, 2013 6:58 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 12:19 PM
Points: 139, Visits: 624
@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.
Post #1514257
Posted Monday, December 9, 2013 9:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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.

Post #1521163
Posted Sunday, January 5, 2014 7:24 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:31 PM
Points: 809, Visits: 5,137
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

Post #1527955
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse