November 6, 2013 at 12:59 pm
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
November 6, 2013 at 2:49 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 6, 2013 at 3:08 pm
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.
November 7, 2013 at 2:06 am
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;-)
November 7, 2013 at 7:46 am
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.
November 7, 2013 at 7:50 am
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".
November 7, 2013 at 8:14 am
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)
November 7, 2013 at 8:22 am
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 8, 2013 at 7:27 am
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?
November 11, 2013 at 5:29 am
what solution u implemented ?
November 14, 2013 at 6:58 am
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.
December 9, 2013 at 9:00 am
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.
January 5, 2014 at 7:24 pm
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...
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply