turning off transaction logging

Question

Post reply

turning off transaction logging

Bill Riggio

SSC Enthusiast

Points: 121
More actions
April 8, 2008 at 4:46 am

#70163

is there anyway to turn off transaction logging programmatically? i need to do
deletes of large numbers of rows & the trans log grows to an unmanageable size.
thanks in advance.
Bill Riggio

Viewing 11 posts - 1 through 10 (of 10 total)

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

8adger Right there with Babe Points: 768 More actions · Answer 1

Depending on a number of things: you could truncate the table if you wanted to completely empty the it.

put the db in simple recovery and delete the records in batches.

Bill Riggio SSC Enthusiast Points: 121 More actions · Answer 2

i can't truncate since some rows need to remain.

was under the impression that simple recovery mode just had to do with the backups of

the trans log. just looking for a way to tell SQL not to play for a rollback since i can always

reissue the deletes on failure.

thanks for responding.

Bill

8adger Right there with Babe Points: 768 More actions · Answer 3

You can't switch off logging it is a fundamental part of SQL Server.

What is the ratio of records you want to keep? Maybe you could store those records in a temp table, truncate the table in question and then import the records from the temp table.

Bill Riggio SSC Enthusiast Points: 121 More actions · Answer 4

approx. 15% is to be deleted from a VLDB. unfortunately it would be way too much data

to copy into a temp table.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

Bill Riggio (4/8/2008)
approx. 15% is to be deleted from a VLDB. unfortunately it would be way too much data
to copy into a temp table.

What you can do is to switch to simple recovery mode, and then break up your delete into smaller transactions (by deleting a fewer number of rows in individual transactions, your log space can be reused, and your log file will not grow) (make sure there are no long running transactions)

You can break up the delete either by using TOP in the delete statement (2005), or by using "set rowcount", or by rewriting the where clause.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Bill Riggio SSC Enthusiast Points: 121 More actions · Answer 6

thanks - i've already broken up the deletes. will simple mode speed up the deletes?

they are being done using the primary clustered index.

GSquared SSC Guru Points: 260824 More actions · Answer 7

Simple mode won't necessarily speed them up, but it will help to keep the log file from growing. Any speed increase will really be from not having to stop, grow the log file, and then keep going.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 8

Bill Riggio (4/8/2008)
thanks - i've already broken up the deletes. will simple mode speed up the deletes?
they are being done using the primary clustered index.

Switching to simple will avoid the transaction log file growing. Since disaster recovery does not seem to be important here (after all you wanted to switch logging off :)), it is OK. This will speed things up.

Another thing to do/consider: disabling non-clustered indexes, foreign keys, etc, and rebuilding them after the deletes.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Bill Riggio SSC Enthusiast Points: 121 More actions · Answer 9

Bill Riggio

SSC Enthusiast

Points: 121

April 8, 2008 at 7:50 am

#799749

thanks all. really appreciate the help.

Bill

aureolin Hall of Fame Points: 3520 More actions · Answer 10

Don't forget to update statistics when you're done!

Steve G. 😀