Disabling Transaction Log

Disabling Transaction Log

Post by Jeffrey Bradsha » Sat, 30 Mar 2002 00:36:25



I've got a large table that we need to add a column to.
When doing the alter table command, the transaction log
fills up and we run out of space. We have turned on auto
grow in the log but we run out of physical disk space.

Is there any way to temporarily turn off the transaction
logging? Essentially I want to do an alter table just like
truncate table in that nothing is save. I realize there's
a potential for problems if the server crashes or dies
during the operation.

Any help would be appreciated.

TIA - Jeffrey.

 
 
 

Disabling Transaction Log

Post by Alle » Sat, 30 Mar 2002 05:14:05


If you are running 2000, set the recovery mode to 'simple'.
If it is 7.0, turn on truncate log on checkpoint.

Quote:>-----Original Message-----
>I've got a large table that we need to add a column to.
>When doing the alter table command, the transaction log
>fills up and we run out of space. We have turned on auto
>grow in the log but we run out of physical disk space.

>Is there any way to temporarily turn off the transaction
>logging? Essentially I want to do an alter table just
like
>truncate table in that nothing is save. I realize there's
>a potential for problems if the server crashes or dies
>during the operation.

>Any help would be appreciated.

>TIA - Jeffrey.

>.


 
 
 

Disabling Transaction Log

Post by Sue Hoegemeie » Sat, 30 Mar 2002 13:36:27


You can change your recovery model as Allen already
suggested which will truncate your log on checkpoints.
However, there is no way to completely turn off logging
transactions to the transaction log.
An alter table doesn't usually hit too hard if you are
adding a nullable column which I would guess isn't the case
for you. To reduce the overhead, make sure not to do this
through Enterprise Manager. You may possibly be able to
reduce problems with your transaction log by allowing
minimally logged operations by setting your recover model to
Bulk-logged (or select into/bulk insert in 7.0) . Then do a
select into to create the table with the column definitions
you need. But this doesn't account for managing relationship
issues or the extra space needed in your data file. Just a
possible option depending on your situation.

--Sue

On Thu, 28 Mar 2002 07:36:25 -0800, "Jeffrey Bradshaw"


>I've got a large table that we need to add a column to.
>When doing the alter table command, the transaction log
>fills up and we run out of space. We have turned on auto
>grow in the log but we run out of physical disk space.

>Is there any way to temporarily turn off the transaction
>logging? Essentially I want to do an alter table just like
>truncate table in that nothing is save. I realize there's
>a potential for problems if the server crashes or dies
>during the operation.

>Any help would be appreciated.

>TIA - Jeffrey.

 
 
 

Disabling Transaction Log

Post by raju girdh » Sun, 31 Mar 2002 04:39:54


Hello,
ALTER TABLE statement will have to be logged. To minimize log impact,
consider setting your Database recovery model to: Simple mode. This will
Permits high-performance bulk copy operations and will reclaims log space
to keep log space requirements small. Check SQL Books Online for more
information regarding "Recovery Models".

Hope this helps.

Raju Girdher MCSE
SQL Support Engineer, Microsoft

Please reply only to newsgroups. When posting, please state the version of

error message text received, if any.

Disclaimer:
This posting is provided AS IS with no warranties, and confers no rights.
You assume all risk for your use. ? 2001 Microsoft Corporation. All rights
reserved.