May 3, 2011 at 11:47 am
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.
May 3, 2011 at 11:51 am
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.
May 3, 2011 at 12:00 pm
bah! I was trying to get around that. Sounds like I'm probably hosed. Thanks, though.
May 3, 2011 at 12:04 pm
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.
May 3, 2011 at 12:21 pm
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.
May 3, 2011 at 12:25 pm
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.
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
May 3, 2011 at 12:25 pm
Thanks, everyone.
May 5, 2011 at 5:36 pm
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?
May 5, 2011 at 6:23 pm
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?
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
May 5, 2011 at 7:35 pm
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.
May 5, 2011 at 7:42 pm
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