Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

delete records without using transaction logs Expand / Collapse
Author
Message
Posted Monday, March 24, 2008 4:41 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:08 AM
Points: 490, Visits: 1,383
I wanted to delete millions of records in the development enviornment based on some date every month. Is there any way to not use transaction log database. I know when we delete, it logs in the transaction log database and then to main database.
If I don't use transaction log then it will be pretty fast for millions of rows.

any idea will be appreciated.




Post #473776
Posted Monday, March 24, 2008 4:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 7:42 AM
Points: 6,743, Visits: 8,515
If these rows make up your entire table, use truncate and you log will not grow.
Truncate will not work if your table is parent table for others.

Otherwise you may want to switch to simple logging and use delete-batches for the operation. Your logfile(s) will not grow if you keep these transactions small enough to fit into the current size.

Declare @BatchSize int

Set @BatchSize = 5000 -- Modify as needed !!!

Set nocount on
declare @RowsDeleted bigint
Declare @MyRowcount bigint
set @RowsDeleted = 0

while 0 = 0
begin
DELETE top ( @BatchSize )
FROM
WHERE

set @MyRowcount = @@rowcount
if @MyRowcount = 0 break
select @RowsDeleted = @RowsDeleted + @MyRowcount

-- just to see it advancing ..
-- % = modulo
if 0.000 = @RowsDeleted % 100000.000 print convert(varchar(26), @RowsDeleted) + ' - ' + convert(varchar(26), getdate(),121)

end

Print '[' + cast(@RowsDeleted as varchar(15)) + '] Rows deleted.'



Since size does matter, did you consider partitioning this table.
That way you may be able to just drop a partition of data. (sliding window)
Check BOL.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #473781
Posted Monday, March 24, 2008 5:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:08 AM
Points: 490, Visits: 1,383
Thanks buddy,
I like the idea of deleting in a batch in that case transaction log files will not grow.
I cannot use truncate option because there is a condition associated with it.

Good stuff.



Post #473788
Posted Monday, March 24, 2008 9:27 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 10, 2014 8:13 AM
Points: 1,278, Visits: 2,204
In addition, deleting a smaller batch is faster. This helps to avoid blocking the other processes as the deletion will not hold the table for too long.

May add waitfor 5 seconds in the while loop after each batch. Just give the other process a chance.

Agree with ALZDBA, partitioning is a way to go for 2005.
Post #473847
Posted Tuesday, March 25, 2008 1:24 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:08 AM
Points: 490, Visits: 1,383
I am trying to delete records in a batch and it's taking forever. there are 65 million rows in one table and I am deleting based on some condition and that table is joining with some other tables.

I came up with another idea.
1. First copy the data to flat file which we want to keep
2. Truncate the table
3. Import data from flat file to the table.

This way is much faster.
any suggestions guys. ?



Post #474356
Posted Tuesday, March 25, 2008 1:33 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 1:07 PM
Points: 347, Visits: 419
This is what I have done in the past; using bcp to drop the data from the table into a file, truncate the table and then reimport the data into the table again.
Tip - order the data when you are exporting it, in the order of the primary key; and then when you are importing it use the -h (hint) switch to define the order. This makes life much nicer.

But as to whether or not it is quicker will depend on the volume of data you are working with.
Post #474363
Posted Wednesday, March 26, 2008 2:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 7:42 AM
Points: 6,743, Visits: 8,515
balbirsinghsodhi (3/25/2008)
I am trying to delete records in a batch and it's taking forever. there are 65 million rows in one table and I am deleting based on some condition and that table is joining with some other tables.

I came up with another idea.
1. First copy the data to flat file which we want to keep
2. Truncate the table
3. Import data from flat file to the table.

This way is much faster.
any suggestions guys. ?


Indeed, it depends on the "fraction" of data you want to keep.
maybe even a temptb may help out. (sort the data according your clustering key columns)
And keep in mind to use an "order by [clust-key-col(s)]" if you insert the data back into the original table and to use bulkinsert if possible.

Doing things this way, you may avoid an extra table maintenance (reindex) step.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #474561
Posted Wednesday, March 26, 2008 12:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 1, 2012 6:02 AM
Points: 10, Visits: 103
This suggestion assumes that the volume of data which you wish to retain is reasonably small.

1. Set simple recovery mode on the host database.
2. Run sp_rename to rename the original table.
3. Create a new table with the original table name without indexes.
4. INSERT INTO the empty table those rows which you with to retain.
5. Create your indexes on the new table.
6. Run sp_createstats or manually create your stats.
7. Grant privileges to match those of the original table along with any constraints.
8. Drop the original table as long as you've coded to check @@ERROR=0.
9. Reset to your original recovery mode.
Post #475010
Posted Wednesday, March 26, 2008 4:06 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:08 AM
Points: 490, Visits: 1,383
Thanks guys.
Here what I have done.

1. Create temp table to store data which I have to keep
My tempdb and Log is on separate drive and have lots of space.
so space is not a problem.
2. Truncate Real Table
3. Drop all the indexes on Real Table
4. Insert into Real table from temp table.
5. Create indexes on Real table

This is faster and doing good.
Thanks for all your help. you guys rock.

I have noticed one thing that playing with large data is not a joke.




Post #475135
Posted Wednesday, March 26, 2008 4:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 1, 2012 6:02 AM
Points: 10, Visits: 103
Thats great.

I would just like to note one thing about this solution. You can potentially lose the data that you want to save if your batch fails following the table truncate and before the insert of the data from tempdb into the previously truncated table has completed since the truncate deallocates pages by removing the pointers to those pages. So, if an page is reallocated it can't be reclaimed.

Just a heads up.:)
Post #475147
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse