Bypass transaction log with DELETE

Question

Bypass transaction log with DELETE

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

I'm a little worried about what will happen when several sources are using it at once while the deletes are running

Good to be worried about these types of things... try it once, manually, with a small number of rows and see. One lock, in and out, really fast. Waits for others to release all locks.

But won't kill you if you don't use it...

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 2

Depending on the % of the data you're deleting from your database you may find that it's actually faster to select the data you do want to keep into new tables, drop the old tables, rename new tables and then recreate your constraints...

Joe

Chris Gierlack SSCrazy Points: 2326 More actions · Answer 3

Yeah....that is part of my battery. I'm looking at my production system and seeing what the ratio of preserved records vs deleted records are and if I can get away with using truncate table then all the better.

Thanks for all the great feedback!

steve block SSCertifiable Points: 7698 More actions · Answer 4

You said that if each client had a staging table you could use truncates. What is preventing you from doing this? If the application requires the staging table to be a specific name, you could look at renaming the individual tables to the require table when you want to use it.

Steve

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 5

... and sometimes its just better to SELECT ... INTO ... the stuff you want to keep and then rebuild indices and other stuff afterwards on that smaller dataset. Just a thought...

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Chris Gierlack SSCrazy Points: 2326 More actions · Answer 6

Does anyone know what will happen if I do a TRUNCATE on a table at the same time I am bulk inserting rows into it? I have to truncate a few tables to clean up all the staging data and the method I am using is, I load all data into a table variable (minus the data I want to clean), I trunc the table and then reinsert. This works well even with larger amounts of data. But now I am wondering under high concurrency if I am going to accidentally trunc the incoming data streams.

What is the easiest way to lock down the table for the duration of the trunc procedure? I thought maybe wrapping it in a transaction but am not sure....

so if I have the following code, how do I lock down myTable for duration of the procedure?

declare @tbltable

(

myCols

)

-- trunc this table

begin try

insert into @tbl(myCols )

select myCols from myTbl with (readpast) where customerid = 1

truncate table myTbl

set identity_insert myTbl on

insert into myTbl (myCols )

select myCols from @tbl

set identity_insert myTbl off

end try

begin catch

select @Message = @Message + ERROR_MESSAGE()

raiserror(@Message, 16, 1)

end catch

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

the TRUNCATE TABLE operation technically locks the entire table first, so if you're doing a single insert operation, it shouldn't interfere. On the other hand could be ugly if you're batching the inserts.

Try this one. I'm sure someone will chime in on transaction isolation level, but I'm thinking you don't want any reads on this....

begin try

set transaction isolation level repeatable read

BEGIN TRANSACTION

insert into @tbl(myCols )

select myCols from myTbl with (readpast) where customerid = 1

truncate table myTbl

set identity_insert myTbl on

insert into myTbl (myCols )

select myCols from @tbl

set identity_insert myTbl off

commit transaction

end try

begin catch

select @Message = @Message + ERROR_MESSAGE()

IF (XACT_STATE()) = -1

BEGIN

-- PRINT

-- N'The transaction is in an uncommittable state.' +

-- 'Rolling back transaction.'

ROLLBACK TRANSACTION;

END;

-- Test whether the transaction is committable.

IF (XACT_STATE()) = 1

BEGIN

-- PRINT

-- N'The transaction is committable.' +

-- 'Committing transaction.'

COMMIT TRANSACTION;

END;

raiserror(@Message, 16, 1)

end catch

drop table @tbl

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?