Hidden Tricks To SQL Server Table Cleanup

  • 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.

  • 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.

  • Good point - I TLOG on the hour so haven't seen a disk space issue so far.

  • So much for my new proof reader:-)

  • BCP will work in some shops but mine is 24*7 so the deletes have to run online.


  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • Interesting idea I have not tried yet, worried about table outage and sp recompiles:-)

  • 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.

  • 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.

  • 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