Deletion of records without logging
-
I have inherited a legacy database in which there is a table containing 7M records, of which only 100k contain valid data.
How can I delete the non-valid data without severely impacting on the log file. Can I delete the records without logging?
Any suggestions please. Regards Zinger
-
This depends on the table design (i.e. foreign keys, etc) but I would (first backup the database) copy the 100K records into a new table:
select * into #t1 from yourtable where .....
Hopefully since you know what you want to delete you also know what you want to keep. Then I would use Truncate Table.
See: http://msdn2.microsoft.com/en-us/library/aa260621(SQL.80).aspx or reference you Books on Line.
Truncate table has minimal logging because it is not deleting rows, but deactivating data pages.
Once the table is truncated you can put the data back:
insert into yourtable (col1, col2, etc....) select * from #t1
NOTE: You can wrap all the above in a transaction and roll it back if if needed. (don't forget to commit).
James.
-
"NOTE: You can wrap all the above in a transaction and roll it back if if needed. (don't forget to commit)."
Not quite true....remember the non-logging/minimal logging effect of the TRUNCATE....that does effect the ability to rollback the overall change!!
-
Actually truncate table is logged, it just the deallocation of the pages that are logged rather than each row, and that can be undone within a transaction.
If you try the following it should prove the point (I tested with it to insure I knew what I was talking about before I responded
you had me doubting my memory. 
if object_id('test_') is not null drop table test_
create table test_ (col1_ int identity, col2_ int)
declare @i_ int
set @i_ = 0
while @i_ < 100
begin
insert into test_ (col2_) values (@i_)
set @i_ = @i_ + 1
end
select * from test_
begin tran
select *
into #temp_
from test_
where col1_ < 20
truncate table test_
set identity_insert test_ on
insert into test_ (col1_,col2_)
select col1_,col2_ from #temp_
select * from test_
rollback
select * from test_
-
The best way I know of is to do this in batches. Delete xx records, backup the T-log, repeat. This way you can prevent the log from growing too much. Ideally you'd do this and then when you're done, run a full backup and then go back to your normal schedule of backups.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply