May 2, 2008 at 9:44 am
The query below was run in QA to delete old rows from a table with 1.5 billion rows, batching it up to avoid blowing the log. The query was cancelled after 4 hours so it started to rollback. I would expect this to just rollback the last 10000 row chunk, but rollback time estimate is over an hour and it looks to me like it is going to roll back everything.
What am I missing?
Declare @Command AS NVARCHAR(4000)
Declare @num int
SELECT @Command = 'delete from SC_SampledNumericDataFact_Table where LocalDateTimeSampled > DATEADD(m,-6, GETDATE())'
Set @num = 1 -- force it into loop first time
While @num <> 0
Begin
begin transaction
set rowcount 10000 -- stop after 10000 rows
SELECT @Command
PRINT @Command
PRINT ' '
EXECUTE sp_executesql @Command
set @num = @@rowcount -- capture no of rows deleted, when this is 0, will drop out of loop
commit transaction
end
---------------------------------------------------------------------
May 2, 2008 at 9:59 am
Not sure, but maybe put the SET ROWCOUNT inside the dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 2, 2008 at 10:12 am
I'd have to agree with Gail on this one. Did you test to see that the Set RowCount was carried to the execution of the dynamic sql?
Also, why use the dynamic sql in the first place? You could just put your delete in the loop. The date calculation is being re-run in each time through the loop. If you want a constant date you should use a variable, if you want the date to change then just put your delete in the loop. It would look like this:
[font="Courier New"]DECLARE @num INT
SET @num = 1 -- force it into loop first time
WHILE @num <> 0
BEGIN
BEGIN TRANSACTION
SET ROWCOUNT 10000 -- stop after 10000 rows
DELETE FROM SC_SampledNumericDataFact_Table WHERE LocalDateTimeSampled > DATEADD(m,-6, GETDATE())
SET @num = @@rowcount -- capture no of rows deleted, when this is 0, will drop out of loop
COMMIT TRANSACTION
END
[/font]
Also, how many rows are you keeping? Would it be better to create a copy of the table structure and insert the rows you want to keep into it and then drop the old table and rename the new one?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 2, 2008 at 10:13 am
Don't know about the log, but if that's the DELETE statement it's removing all your current records and leaving the ones older than 6 months.
May 2, 2008 at 10:18 am
Todd Engen (5/2/2008)
Don't know about the log, but if that's the DELETE statement it's removing all your current records and leaving the ones older than 6 months.
Great catch Todd. I really need to read the queries better. The > should be a < I am sure.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 2, 2008 at 11:02 am
Todd Engen (5/2/2008)
Don't know about the log, but if that's the DELETE statement it's removing all your current records and leaving the ones older than 6 months.
very good catch, could have been nasty. I,m pleased the whole thing rolled back now.
I need to look into this some more, turns out there are no records older than 6 months (using column provided anyway), its the sheer quantity of data being captured. This is the SQL backend MI database for MOM, a famous pain tn the ****. someone set MOM to capture disk i/o stats across all servers, EVERY SECOND. Now its in the database.
This arose when the architect asked for a method to batch up a delete statement, I guess dynamic SQL was added into logic because thats how its done in the stored proc provided with MOM to groom data (it does it in one big delete). Whatever delete stament we end up using I'll put it into the loop directly and not use dynamic sql.
hard to say right now what percentage of records will be deleted, but anyway we do not have space for a temporary duplication of data. (table is 450GB big) almost the whole database.
If anyone can give a definite reason why it all rolled back would appreciate it, still intrigues me, my logic in using rowcount is sound yes?
---------------------------------------------------------------------
May 2, 2008 at 12:27 pm
I don't know why it would rollback all unless it considered them all being nested within a a larger transaction which then rolled back when you killed one. I don't have any logic for this, I'm just throwing out ideas.
As far as using SET ROWCOUNT, I don't think there is anything wrong with that, but when I have had to do staged deletes I usually try to do it using a date, if I have one. So instead of using the loop as you have it, I would do something like this:
DECLARE @keep_date DATETIME,
@start_date DATETIME,
@end_date DATETIME
SET @keep_date = DATEADD(MM, -6, GETDATE())
SELECT
@start_date = MIN(LocalDateTimeSampled )
FROM
SC_SampledNumericDataFact_Table
SET @end_date = DATEADD(DD, 1, @start_date)
WHILE @end_date < @keep_date
BEGIN
BEGIN TRANSACTION
DELETE
FROM
SC_SampledNumericDataFact_Table
WHERE
LocalDateTimeSampled >= @start_date AND LocalDateTimeSampled < @end_date
SET @start_date = @end_date
SET @end_date = DATEADD(DD, 1, @start_date)
COMMIT TRANSACTION
END
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 2, 2008 at 12:44 pm
thanks Jack, I like the idea of taking chunks out of this table a day at a time.
I'll post results which will be next week now, way too much else going on this weekend to tackle this long running process
thnks for your time and effort, and also todd and gail.
george
---------------------------------------------------------------------
May 2, 2008 at 1:43 pm
If this is being run from an Agent Job, then I believe that each step is wrapped in its own transaction. You would have to use your own Explicit transactions to limit the ROLLBACK.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 9, 2008 at 5:47 am
Hi people,
it was the dynaminc sql causing the loop to be wrapped in an outer transaction, causing the whole thing to roll back when cancelled.
I put the actual delete statement directly into the loop and all running well.
cheers for your input
george
---------------------------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply