Suggestions for deleting large amount of records from a live, high-availability table

  • I have a database, specifically ASPState, which the prior DBA moved over from another server without also moving over the job to delete expired sessions. We now have a table, ASPStateTempSessions, which has several hundred millions rows that is becoming increasingly difficult to maintain. We have a very 24/7 high-availability website that, unfortunately, is dependent on this non-redundant table, so doing a delete on any sort of scale isn't viable. The one thing that I have tried to get around this is creating a duplicate table and moving non-expired sessions over to it, with the idea of swapping the table names afterwards and then just dropping the enormous table, a la:

    declare @save_recs datetime

    set @save_recs = getdate()

    insert into ASPStateTempSessions2 [fields]

    select [fields] from ASPStateTempSessions (nolock) where expires > @save_recs

    Unfortunately, after about 2 minutes, I get the error "Could not continue scan with NOLOCK due to data movement."

    I've read a couple of articles that say that this could be due to table corruption, which seems a possibility, as I'm unable to do a dbcc checktable. Either way, I'm just trying to figure out a way to get this table down to a reasonable size without dropping 250,000 sessions in a very ungraceful manner.

  • I'd start a look and delete by batches of maybe 10 000 ended sessions (or whatever runs under 0.5 seconds).

    wait 2-3 seconds for the server to catch up and affect the site as little as possible.

    Repeat untill you're done.

    Make sure you have frequent log backups while you do this cause it's going to grow, and fast.

  • bah! I was trying to get around that. Sounds like I'm probably hosed. Thanks, though.

  • It'll get you there... eventually.

    The best option was copy / truncate, reimport but that table must see way too much action to get this to work.

    I'd go the safe route, even if it takes 1-2 days to get fully under control, you're garanteed to get there and not lose anything.

  • You can always run this as an SQL job. You can then vary the batch size (in the script) and frequency (how often the jobs runs). This is also restartable so you don't have to leave a session open or worry about it crashing just after you have left for the day/the beach/the weekend/on holiday. 😎

    Just a thought.

    Regards, Mark.

  • If you need to avoid lock escalation, don't go above 5000 rows per pass:

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

    See about midway down for a description of when the thresholds are hit and locks escalate.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Thanks, everyone.

  • So even with tuning down the deletes to just 2500 per run, we've run into some lengthy table locks which have already resulted in our website being inaccessible. I've been asked to do whatever it takes to get this fixed tonight, even if we drop sessions.

    My plan is to create an exact copy of the SessionStateTemp table (indexes, constraints, etc.) and then rename the old (bad) one to _bad and then rename the new one to the proper name. In theory, this shouldn't be a problem. Does this raise warning flags to anyone?

  • coronaride (5/5/2011)


    So even with tuning down the deletes to just 2500 per run, we've run into some lengthy table locks which have already resulted in our website being inaccessible. I've been asked to do whatever it takes to get this fixed tonight, even if we drop sessions.

    My plan is to create an exact copy of the SessionStateTemp table (indexes, constraints, etc.) and then rename the old (bad) one to _bad and then rename the new one to the proper name. In theory, this shouldn't be a problem. Does this raise warning flags to anyone?

    In one or two ways. I'm assuming between creation and rename you're going to move all the records you want to keep. Are you sure the data will not change between step 1 and 2?

    EDIT: Oh, yeah, what's your Foreign Keys look like?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • We don't care about keeping the data - it's just session state stuff. And there are only a couple constraints, but I've already mapped them out on the new table.

  • Fire at will. Maybe if you have a datetime stamp you could reinsert those as 2nd batch after the move is done and save them. But that's all I have to add at this point.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply