Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DELTING HUGE No. of ROWS


DELTING HUGE No. of ROWS

Author
Message
funooni
funooni
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 294
Dears ,

I have to delete data from HUGETABLE which has around 700 million rows prior to 1st May 2014. This table has no Index, however, it has an ID column.

Now in order to avoid the log growth, I wrote the below code. Assuming that it will delete 1 million rows and at any time I cancel it; it will rollback only the last transaction. But on the other hand when I cancel it; it starts rolling back all the rows back into the table.

Any idea, how can I achieve it without running out of LOG File Space ?
Next thing I have in my mind is to schedule a job to delete around a milllion rows every 10 minutes and truncate the log at the end of the job.

Appreciate experts feedback.


begin try drop table #counter end try begin catch end catch
create table #counter (id int )
   insert into #counter SELECT top 1 ID From HUGETABLE with (nolock) where DateTime < '2014-05-01'


while (select COUNT (1) from #counter) > 0
begin
   begin transaction deletemol
   truncate table #counter
   
   insert into #counter SELECT top 10000000 ID From HUGETABLE with (nolock) where DateTime < '2014-05-01'

   if exists (select top 1 1 from #counter )
   begin
    delete from HUGETABLE where ID in (select ID from #counter)
   end
   truncate table #counter
   insert into #counter
   select top 1 ID From HUGETABLE with (nolock) where AuditDateTime < '2014-05-01'
   select GETDATE ()

   commit transaction deletemol   
   
   DBCC SHRINKFILE (N'DB_log' , 0, TRUNCATEONLY)   

END


Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37935
funooni (8/8/2014)
Dears ,

I have to delete data from HUGETABLE which has around 700 million rows prior to 1st May 2014. This table has no Index, however, it has an ID column.

Now in order to avoid the log growth, I wrote the below code. Assuming that it will delete 1 million rows and at any time I cancel it; it will rollback only the last transaction. But on the other hand when I cancel it; it starts rolling back all the rows back into the table.

Any idea, how can I achieve it without running out of LOG File Space ?
Next thing I have in my mind is to schedule a job to delete around a milllion rows every 10 minutes and truncate the log at the end of the job.

Appreciate experts feedback.


begin try drop table #counter end try begin catch end catch
create table #counter (id int )
   insert into #counter SELECT top 1 ID From HUGETABLE with (nolock) where DateTime < '2014-05-01'


while (select COUNT (1) from #counter) > 0
begin
   begin transaction deletemol
   truncate table #counter
   
   insert into #counter SELECT top 10000000 ID From HUGETABLE with (nolock) where DateTime < '2014-05-01'

   if exists (select top 1 1 from #counter )
   begin
    delete from HUGETABLE where ID in (select ID from #counter)
   end
   truncate table #counter
   insert into #counter
   select top 1 ID From HUGETABLE with (nolock) where AuditDateTime < '2014-05-01'
   select GETDATE ()

   commit transaction deletemol   
   
   DBCC SHRINKFILE (N'DB_log' , 0, TRUNCATEONLY)   

END



Looks like you need to delete 700 million rows of data, how many rows of data are you keeping?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
funooni
funooni
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 294
around 150 million rows will be kept i.e. last three months data.

I am thinking about taking those rows into another table and then truncating/dropping the table and renaming the actual table.

But the problem is that this table is used for insertions, so there is a chance of data loss
funooni
funooni
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 294
When I cancelled the Running Query, shouldn't the SQL Server rollback only the rows of last transaction. But it is rolling back the rows which are deleted and committed in earlier iterations.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37935
There are other issues with your query, but lets not worry about that just yet. Another question, what edition of SQL Server are you running?

Also, is the intent to keep 3 months of data in the table going forward?

If so, is that 3 months plus the current month?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
sgmunson
sgmunson
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2837 Visits: 3757
If you cancel a query in SSMS, then the entire query's actions get rolled back, although I don't know what would happen if there were separate batches within what was executed. Just using a WHILE loop isn't going to get you past that problem.

Steve
(aka sgmunson)
:-):-):-)
Health & Nutrition
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44983 Visits: 39870
I have to get to work but here's a 60,000 ft view of what I would do...

1. DO A FULL BACKUP!!!!!!!!!!!!
2. MAKE SURE POINT-IN-TIME BACKUPS ARE BEING TAKEN111!
3. Create an empty identical table.
4. Do a cascaded-rename (takes about 63ms) to rename the old table to something else and rename the new table to what the old table was named. This new table will continue in the place of the old table.
5. Insert the desired old rows from the old table into the new table using a WHILE loop that does it in 3 million row batchs (most systems will reach a tipping point shortly after that so I wouldn't go much higher if at all).
6. When you're absolutely sure that everything worked correctly, DROP the old table.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
funooni
funooni
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 294
Ahan , so that's why it did not go the way I thought. SSMS entire query is getting rolled back
funooni
funooni
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 294
Jeff Moden (8/8/2014)
I have to get to work but here's a 60,000 ft view of what I would do...

1. DO A FULL BACKUP!!!!!!!!!!!!
2. MAKE SURE POINT-IN-TIME BACKUPS ARE BEING TAKEN111!
3. Create an empty identical table.
4. Do a cascaded-rename (takes about 63ms) to rename the old table to something else and rename the new table to what the old table was named. This new table will continue in the place of the old table.
5. Insert the desired old rows from the old table into the new table using a WHILE loop that does it in 3 million row batchs (most systems will reach a tipping point shortly after that so I wouldn't go much higher if at all).
6. When you're absolutely sure that everything worked correctly, DROP the old table.


Thanks Jeff

Backup is done and point in time backups are being taken

I will follow your suggestion. I believe 63 ms would mean a very slight downtime might be faced by some trigger or end user application.
By the way the table is hit by a trigger on another table so the ultimate end user might not face anything.

Any further suggestion about the downtime ? I mean what should I be expecting during that 63 ms ?

Thanks again.
funooni
funooni
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 294
Lynn Pettis (8/8/2014)
There are other issues with your query, but lets not worry about that just yet. Another question, what edition of SQL Server are you running?

Also, is the intent to keep 3 months of data in the table going forward?

If so, is that 3 months plus the current month?


Yes its the current month and last three months i.e. May, June, July and August.

I am using SQL Server 2008 R2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search