Session Transaction Logging
Gavin Gibson
I have an archive database from which I move data on to tape and then
delete the data from my archive db to reclaim space.
I am therefore not to bothered about logging my operations i.e. if
they went wrong somewhere I'd just restore the DB from tape + I don't
have to be in any rush to do this.
In this instance logging of my delete just slows it down and takes up
space (which I don't have much off and am in danger of running out of)
- also breaking the delete down in to smaller parts is a real hassle
in this situation. I've read every possible thing I can find on this
and the answer is that I can't turn off logging for a session, is this
true?
If this is the case I guess my only other way to do an operation
without logging/or with reduced logging would be to output the table
to file and load via BCP. Or to cut down on total logging I could
insert my smaller data set I want to keep in to a temporary table,
drop the original, recreate it and insert from the temporary however
if I could just switch logging of for this session my life would be a
lot grand and dandier...
Cheers & Thanks for help in advance
Gavin
John Bandettini
Depending on the version of SQL Server you are using, you can either
set truncate log on checkpoint to true or set the database to simple
recovery while you do this process. While not stopping logging it will
reduce the impact of it and save precious space on your log file.
If you want to return to 'normal' afterwards you will need to do a
full backup to make any transaction logs usable.
Regards
John