March 6, 2013 at 4:31 pm
But!!!!!
ORDER IS IGNORED IN A VIEW, starting with SQL 2005. Even with Top 100 Percent specified, starting with (I believe) SQL 2008. See Books Online and many online posts. How does that affect this article?
(From one blog entry: When you save a VIEW definition, the ORDER BY is ignored on the returnset. That is by SQL ANSI standards.)
That's what I understand. If that's wrong, please let me know. Thanks.
March 6, 2013 at 4:53 pm
This will work if you want to delete all rows. The article keeps the last 6 days of data so you have to check how many rows left.
March 6, 2013 at 4:54 pm
Good point - I TLOG on the hour so haven't seen a disk space issue so far.
March 6, 2013 at 4:55 pm
So much for my new proof reader:-)
March 6, 2013 at 4:58 pm
BCP will work in some shops but mine is 24*7 so the deletes have to run online.
Thanks,
March 6, 2013 at 5:07 pm
True enough if back dates are inserted. DBAs need to understand their data, in these tables the event_date is alway 'right now' upon insert so there is no worry about back dates.
March 6, 2013 at 5:11 pm
I am a big fan of the 2 temp table approach and wrote about that on a previous article published at SQL Central. The difference here is to pull the rows in primary key sequence using an ORDER BY so I had to switch to this loop thru the view approach.
March 6, 2013 at 5:16 pm
Great question. I run the child table cleanup jobs first then the parent job. Just to be safe I keep more days (16) in the parent table and just (7) in the child tables. You can adjust the parent value to retain to what works. Occasionally I still get delete failure so I handle those manually.
March 6, 2013 at 5:20 pm
If I know there are 50 million rows to remove and I set @cnt = 1,000,000 then I can safely run the job 49 times and know that 1 million rows will remain.
March 6, 2013 at 5:29 pm
Great question - actually this will impact replication. If you want the deletes to flow through to subscriber no issue other than some latency if not you can modify the replication delete stored procedure to not delete when these jobs run. I had to do this very thing on several articles.
You can find the replication stored procedures typically on the subscriber database's stored procedure folder with names like sp_MSdel_dbotable. Remember to restore the stored procedure once the deletes are done.
March 6, 2013 at 5:37 pm
I size the TEMPDBs at 15GB each which is considerably larger than any single delete job can hit. In addition I keep 40B free on the TEMPDB disk drive for expansion just in case. Recommend when implementing these delete jobs to monitor usage to see if there are any resources issues.
March 6, 2013 at 5:39 pm
Interesting idea I have not tried yet, worried about table outage and sp recompiles:-)
March 6, 2013 at 5:42 pm
I use QUEST Spotlight to monitor locking - this technique holds locks very briefly since it is only 2000 rows per transaction. Spotlight recycles every 6 seconds typically there is no blocking, in the rare cases were blocking occurs I lower the 2000 until it disappears.
Good question.
March 6, 2013 at 6:00 pm
Try this.
CREATE VIEW dbo.test as (SELECT top (100) * from table ORDER BY primary_key_column). Look at the result they will be ordered.
March 7, 2013 at 2:24 am
I've quickly scanned this thread and I'm surprised that no one has mentioned that this is one of the prime reason partitioned tables exist.
In this kind of situation I would straight away suggest a partitioned table as it has none of the overheads of using DELETE methods. Of course you need to take into account the limitations of changing to a partitioned table as well.
Viewing 15 posts - 46 through 60 (of 95 total)
You must be logged in to reply to this topic. Login to reply